Date:         Wed, 17 Jul 2002 16:10:07 -0400
Reply-To:     Howard_Schreier@ITA.DOC.GOV
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard_Schreier@ITA.DOC.GOV
Subject:      Re: Another SQL -> SAS conversion question...

You can't.

In the DATA step, the WHERE statement can only specify a simple filter,
which is applied "upstream" to every input SAS dataset.

In other words, coding a WHERE statement is the same as coding WHERE=
dataset options with the same conditions for each and every SAS dataset used
as input.

Here is a simple example:

   data left;
   input a @@;
   cards;
   5 4 3 2
   ;
   data right;
   input b @@;
   cards;
   1 2 3 4
   ;
   data both;
   merge left right;
   where a>b;
   run;

One might expect to produce BOTH with two observations, (5,1) and (4,2).
Instead, there are messages in the log:

   ERROR: Variable b is not on file WORK.LEFT.
   ERROR: Variable a is not on file WORK.RIGHT.

On Wed, 17 Jul 2002 13:58:43 -0400, Witness <bmeyer67@CALVIN.EDU> wrote:

>I have two tables. One is generated by PROC IMPORT which reads values
>from a file as part of a dynamic configuration of the script. The other
>is generated from a database.  I want to remove any values that are not
>in the file from the other database.
>
>To do this in SQL I use the following:
>
>PROC SQL;
>        CREATE TABLE InputData AS
>                SELECT * FROM SecondTable
>                        WHERE ID IN (SELECT ID FROM FirstTable);
>
>I know I can use a data step to create the new table, but how would I
>gain the action of the IN clause in the WHERE statement?
>
>Thanks in advance,
>
>Benjamen R. Meyer