I have two data sets A and B that contain a list of ID values. I would like to create data set C that has all ID values present in data set A except those that are also present in data set B. This works unless I have a duplicate record in A that is also present in B (ID value 3 here). The example produces data set C that has ID values 1, 3, and 5. How do I ensure I get a data set C with only ID values 1 and 5?
data a; input id; cards;
1
2
3
3
5
6
;
run;
data b; input id; cards;
2
3
4
6
6
;
run;
proc sql;
create table c as
select id from a
except all
select id from b;
quit;
Thank you!
Did you try
proc sql; create table c as select id from a except select id from b ; quit;
when you are using All with except then you get "left overs" when there are more of a value in the first set than the second.
proc sql;
create table c
as select a.*
from a a where a.id not in (select distinct id from b);
quit;
Did you try
proc sql; create table c as select id from a except select id from b ; quit;
when you are using All with except then you get "left overs" when there are more of a value in the first set than the second.
When you add ALL to UNION, INTERSECT or EXCEPT SQL set operations, you state that you want to skip a final scan of the resulting table to remove duplicate entries. It speeds up the operation, but you should only use it when the input tables already contain distinct records or you actually want to keep duplicates.
Examples of ALL processing:
data a; input id; cards;
1
2
3
3
5
6
7
7
7
;
run;
data b; input id; cards;
2
3
4
6
6
7
7
;
run;
proc sql;
select id from a
except all
select id from b;
select id from a
union all
select id from b;
select id from a
intersect all
select id from b;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.