topic Re: Keep only ID duplicates in SAS Programming

Keep only ID duplicates

Satori — Thu, 19 Jan 2023 13:44:24 GMT

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

Re: Keep only ID duplicates

PaigeMiller — Thu, 19 Jan 2023 13:41:50 GMT

Please provide data that illustrates the problem. Right now, there is no ID which has both U2 and C2.

Re: Keep only ID duplicates

Satori — Thu, 19 Jan 2023 13:43:32 GMT

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

Re: Keep only ID duplicates

PaigeMiller — Thu, 19 Jan 2023 13:53:49 GMT

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;

Re: Keep only ID duplicates

Satori — Thu, 19 Jan 2023 13:59:20 GMT

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.

Re: Keep only ID duplicates

PaigeMiller — Thu, 19 Jan 2023 14:11:01 GMT

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

Re: Keep only ID duplicates

data_null__ — Thu, 19 Jan 2023 14:12:14 GMT

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;