topic Re: PROC SQL Question in SAS Procedures

PROC SQL Question

Altal — Thu, 21 Nov 2013 20:04:57 GMT

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!

Re: PROC SQL Question

Reeza — Thu, 21 Nov 2013 20:08:28 GMT

Do a join instead

Re: PROC SQL Question

snoopy369 — Thu, 21 Nov 2013 20:09:19 GMT

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.

Re: PROC SQL Question

Altal — Thu, 21 Nov 2013 20:09:52 GMT

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

Re: PROC SQL Question

snoopy369 — Thu, 21 Nov 2013 20:13:24 GMT

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