PROC SQL Question

[Altal]

Hello everybody,

I'm trying to select observations from one dataset based on certain criteria from another dataset in proc sql. I'm using WHERE EXISTS statement to filter-out.

My question, is there a way to include a variable (or more) from inside the WHERE EXISTS subquery into the main query?

Here is an example.

I'm selecting observations from have1 if visitDt was before or equals to trtDt in have2.

The query runs well, but once I request any variable from the subquery (e.g.   b.trtDt), the program will err.

I was wondering if there a way to modify the code to have variable(s) from the subquery appear in the main query results.

data have1;

input id visit visitDate mmddyy10.;

format visitDate mmddyy10.;

cards;

1 1 01/01/2012

1 2 02/01/2012

1 3 03/01/2012

1 4 04/01/2012

1 5 05/01/2012

2 1 01/01/2012

2 2 02/01/2012

2 3 03/01/2012

2 4 04/01/2012

2 5 05/01/2012

;

run;

data have2;

input id trtDt mmddyy10.;

format trtDt mmddyy10.;

cards;

1 02/01/2012

2 04/01/2012

;

run;

proc sql;

  select id, visit

  from have1 a

  where exists      (select * from have2  b

                           where visitDate <= trtDt                             

                           and a.id=b.id );    

quit;

Thanks for help!

[Reeza]

Do a join instead

[Altal]

Agree join will give required result ; but just wondering if possible with "where exists"....

[snoopy369 · Accepted solution]

No, WHERE EXISTS is simply checking a table - it doesn't bring anything along with it except the result of the EXISTS query.

[snoopy369]

You would have to do a join instead of a where exists query.  An inner join between a and b could be constructed that works effectively identically to the where exists.

IE:

proc sql;

create table want as

  select a.id, visit

  from have1 a, have2 b

  where A.id=B.id and a.visitDate le B.trtDt; 

quit;

Now you can access B's variables i fyou want on the select statement.  It's not perfectly identical to where exists, but depending on the data it is usually equivalent.  It may act differently if B has duplicates per ID, in particular.