- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 04-01-2011 02:55 PM
(9312 views)
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
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
5 REPLIES 5
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Those worked great. Thanks Reeza.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
[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
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