DATA Step, Macro, Functions and more

using min to select one row from each group

Reply
Contributor
Posts: 32

using min to select one row from each group

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
Super User
Posts: 19,822

Re: using min to select one row from each group

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;
Contributor
Posts: 32

Re: using min to select one row from each group

Those worked great. Thanks Reeza.
Super Contributor
Posts: 578

Re: using min to select one row from each group

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;
Contributor
Posts: 32

Re: using min to select one row from each group

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.
Super User
Posts: 10,041

Re: using min to select one row from each group

[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
Ask a Question
Discussion stats
  • 5 replies
  • 1107 views
  • 0 likes
  • 4 in conversation