topic Keep unique IDs only in SAS Programming

Keep unique IDs only

Satori — Tue, 21 Feb 2023 14:14:24 GMT

I have a dataset like this (below). The variable code takes 4 possible values

The ID variable is not unique. There can be an ID with only code of the 4 possible, or with more than one code. I want to keep only the unique IDs. So if an ID is repeated I want to remove it. Objective is to keep all IDs that have only one code.

Obs ID code

1 AE0000037163 C1
2 BN0000037282 U2
3 CD0000037693 U1
4 RS0000037738 U2
5 RS0000037738 C2

. . .

Re: Keep unique IDs only

PaigeMiller — Tue, 21 Feb 2023 14:49:39 GMT

Use PROC SORT

proc sort data=have nouniquekey uniqueout=want;
    by id;
run;

Re: Keep unique IDs only

awesome_opossum — Tue, 21 Feb 2023 17:13:55 GMT

proc sql; create table data1
as select distinct
*
from data;
quit; 

proc sql; create table data2
as select
*,
count(code) as k
from data1
group by id; 
quit; 

data data3; set data2; 
if k = 1; 
run;

Re: Keep unique IDs only

ChanceTGardener — Tue, 21 Feb 2023 20:24:30 GMT

data have;
input obs id $ code $;
datalines;
1 AE0000037163 C1
2 BN0000037282 U2
3 CD0000037693 U1
4 RS0000037738 U2
5 RS0000037738 C2
;

proc sql;
create table want as 
select distinct id
from have
group by id
having count(code) eq 1;
quit;

Re: Keep unique IDs only

Kurt_Bremser — Wed, 22 Feb 2023 06:22:54 GMT

data want;
set have;
by id;
if first.id and last.id;
run;