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!
No, WHERE EXISTS is simply checking a table - it doesn't bring anything along with it except the result of the EXISTS query.
Do a join instead
Agree join will give required result ; but just wondering if possible with "where exists"....
No, WHERE EXISTS is simply checking a table - it doesn't bring anything along with it except the result of the EXISTS query.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.