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...
Have a look into the EXCEPT operator
Have a look into the EXCEPT operator
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;
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.