DATA Step, Macro, Functions and more

Conversion from Merge to PROC SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

Conversion from Merge to PROC SQL

Here is the code:

 

data work.teenstudents;                              
  set sashelp.class;                                
  where age > 12;
run; 

data teenstudents2;
  set sashelp.class;
  where age > 14;
run;

data want;
  merge teenstudents(in=ts) teenstudents2(in=ts2);
  by name;
  keep name;
  if ts and not ts2;
run;

As you can see, I want all the names that are in teenstudents that are not in teenstudents2. The above merge works perfectly to accomplish that. But how do I do this in PROC SQL? I keep trying but I'm not getting the correct results. I left joined teenstudents with teenstudents2 on the condition that the names from each table do not equal each other, but I'm not getting the correct results...


Accepted Solutions
Solution
‎11-20-2016 05:25 PM
Respected Advisor
Posts: 3,887

Re: Conversion from Merge to PROC SQL


All Replies
Solution
‎11-20-2016 05:25 PM
Respected Advisor
Posts: 3,887

Re: Conversion from Merge to PROC SQL

Respected Advisor
Posts: 3,887

Re: Conversion from Merge to PROC SQL

You can also use a left join and then filter the result to only pick-up records where the name from teenstudent2 is missing (=all records where there was no match to teenstudent2). Such an approach will return the same number of rows as long as the table relationship is not 1:M or M:M

data work.teenstudents;                              
  set sashelp.class;                                
  where age > 12;
run; 

data teenstudents2;
  set sashelp.class;
  where age > 14;
run;

proc sql;
  select a.*
    from 
      teenstudents a
    left join
      teenstudents2 b
      on a.name=b.name
    having b.name is NULL
  ;
quit;
☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 152 views
  • 0 likes
  • 2 in conversation