topic Re: using min to select one row from each group in SAS Programming

using min to select one row from each group

DanD — Fri, 01 Apr 2011 18:55:52 GMT

I want to select one row for each id based on the min(date). My data looks like this:

id code date
1 38 200912
1 AG 201010
2 17 200911
2 09 200910
2 36 201010
3 12 201009

The result set should look like this:
1 38 200912
2 09 200910
3 12 201009

How do I do this?

Thanks,

Dan

Re: using min to select one row from each group

Reeza — Fri, 01 Apr 2011 20:08:00 GMT

Two step proc sort.

proc sort data=have;
by id descending date;
run;

proc sort data=have nodupkey out=want dupout=duplicates;
by id;
run;

OR the second step can be the one below.

data first;
set have;
by id;
if first.id then output;
run;

Re: using min to select one row from each group

DBailey — Fri, 01 Apr 2011 20:11:12 GMT

one step, if you don't mind potential duplicates:

proc sql;
select
t1.id,
t1.code,
t1.date
from
work.tabA t1
where
t1.date=(select min(date) from work.tabA where id=t1.id);
quit;

Re: using min to select one row from each group

DanD — Fri, 01 Apr 2011 20:46:14 GMT

Those worked great. Thanks Reeza.

Re: using min to select one row from each group

DanD — Fri, 01 Apr 2011 20:47:38 GMT

I tried it but you were right, it did have a few dupes. I need to have only one row per id. Reeza's code in an above message worked for what I need. Thanks for replying.

Re: using min to select one row from each group

Ksharp — Wed, 06 Apr 2011 09:41:31 GMT

[pre]
data temp;
input id code $ date ;
cards;
1 38 200912
1 AG 201010
2 17 200911
2 09 200910
2 36 201010
3 12 201009
;
run;
proc sql;
select *
from temp
group by id
haveing date eq min(date)
;
quit;
[/pre]

Ksharp