SAS select or keep only one of duplicate observations

hellorc — Wed, 21 Sep 2022 13:35:43 GMT

Hello SAS community, I have a quick question. Consider the following simple data:

data have;
input id city $ week money @@;
datalines;
1 A 1 100
1 A 2 105
1 A 5 108
1 B 5 206
1 B 7 99
1 C 8 101
1 C 10 110
2 B 5 202
2 C 5 100
2 D 8 95
2 E 8 98
;
run;

I would like the observations for each ID have unique week. If for each subject there are duplicate 'week', the 'week' with larger money variable would be kept. So:

data want;
input id city $ week money @@;
datalines;
1 A 1 100
1 A 2 105
1 B 5 206
1 B 7 99
1 C 8 101
1 C 10 110
2 B 5 202
2 E 8 98
;
run;

Can this be done via some data steps? Or SQL is a must? Might someone be willing to assist? Thank you in advance!

Re: SAS select or keep only one of duplicate observations

Kurt_Bremser — Wed, 21 Sep 2022 14:10:17 GMT

Sort, then use a data step with BY:

proc sort data=have,
by id week money;
run;

data want;
set have;
by id week;
if last.week;
run;

Untested, posted from my tablet.

topic Re: SAS select or keep only one of duplicate observations in New SAS User

SAS select or keep only one of duplicate observations

Re: SAS select or keep only one of duplicate observations