topic Re: Conversion from Merge to PROC SQL in SAS Programming

Conversion from Merge to PROC SQL

JediApprentice — Sun, 20 Nov 2016 22:08:28 GMT

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...

Re: Conversion from Merge to PROC SQL

Patrick — Sun, 20 Nov 2016 22:16:27 GMT

Have a look into the EXCEPT operator

http://support.sas.com/documentation/cdl/en/sqlproc/69049/HTML/default/viewer.htm#n0vo2lglyrnexwn14emi8m0jqvrj.htm

Re: Conversion from Merge to PROC SQL

Patrick — Sun, 20 Nov 2016 22:44:24 GMT

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;