BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Altal
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
snoopy369
Barite | Level 11

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

View solution in original post

4 REPLIES 4
Reeza
Super User

Do a join instead

Altal
Calcite | Level 5

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

snoopy369
Barite | Level 11

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

snoopy369
Barite | Level 11

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 813 views
  • 6 likes
  • 3 in conversation