"Subtracting" one data set from another doesn't work with triplicate records

[kpberger]

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!

[smantha]

proc sql;

create table c

as select a.*

from a  a where a.id not in (select distinct id from b);

quit;

[ballardw · Accepted solution]

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.

[PGStats]

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;