Help using Base SAS procedures

PROC SQL Question

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

PROC SQL Question

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!


Accepted Solutions
Solution
‎11-21-2013 03:13 PM
Regular Contributor
Posts: 244

Re: PROC SQL Question

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


All Replies
Super User
Posts: 17,836

Re: PROC SQL Question

Do a join instead

Contributor
Posts: 62

Re: PROC SQL Question

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

Solution
‎11-21-2013 03:13 PM
Regular Contributor
Posts: 244

Re: PROC SQL Question

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

Regular Contributor
Posts: 244

Re: PROC SQL Question

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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