I have a dataset like this (below). The variable code takes only two values ('C2', 'U2')
The ID variable is not unique. There can be an ID with only code U2, only code C2 and with both codes. I want to keep only the last one of these, meaning I want to keep the observations with the same ID and different codes, and drop all IDs that have only one code.
Obs ID code
1 AE0000037163 U2
2 AE0000037282 U2
3 AE0000037693 U2
4 AE0000037738 U2
5 AE0000037738 C2
I will assume you want to see the NON-unique ID values.
data have;
input Obs ID:$12. code $;
cards;
1 AE0000037163 U2
2 AE0000037282 U2
3 AE0000037693 U2
4 AE0000037738 U2
5 AE0000037738 C2
;
run;
proc print;
proc sort data=have nounikey out=dups uniout=_null_;
by id;
run;
proc print;
run;
Please provide data that illustrates the problem. Right now, there is no ID which has both U2 and C2.
From now on, please provide data as WORKING data step code, as I have shown below in creating data set named HAVE.
data have;
input Obs ID $12. code $;
cards;
1 AE0000037163 U2
2 AE0000037282 U2
3 AE0000037693 U2
4 AE0000037738 U2
5 AE0000037738 C2
;
proc sql;
create table want as select id
from have
group by id
having sum(code='U2')>0 and sum(code='C2')>0;
quit;
@Satori wrote:
After running your suggested code, I ended up with a list of observations that only shows the ID column, and none of the IDs are duplicate.
I don't understand this. Show us what you see. Show us what you want to see.
I will assume you want to see the NON-unique ID values.
data have;
input Obs ID:$12. code $;
cards;
1 AE0000037163 U2
2 AE0000037282 U2
3 AE0000037693 U2
4 AE0000037738 U2
5 AE0000037738 C2
;
run;
proc print;
proc sort data=have nounikey out=dups uniout=_null_;
by id;
run;
proc print;
run;
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.