3

For example I have a dataset as below:

id Date
1  2000/01/01
1  2001/01/01
1  2002/01/01
2  2003/01/01

By datastep or sql, how could I get the record with id = 1 and latest Date 2002/01/01? Help is appreciated and thanks in advance.

Luke Henz
  • 137
  • 3
  • 5
  • 12

5 Answers5

6

Try this sql.

select id,max(Date)
from yourtable
group by id;
ATR
  • 2,160
  • 4
  • 22
  • 43
3

If you want the entire record and the data is sorted as shown (BY id and DESCENDING date), you can use this data step:

data want;
   set have;
      by id; /* you don't need to specify date here */
   if last.id;
run;

This give you the most recent record for each id.

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
2

you can try:

proc sql;
create table my id as
select id,max(Date)
from yourtable
where id=1;
quit
2

You can try this as well

proc sql;
create table my id as
select id,Date
from yourtable
where Date=(select max(Date) where id = 1 )
quit
0
/*Sort your data by id and descending date then*/

data want;
   set have;
      by id; /* you don't need to specify date here */
   if first.id;
run;
Ashish Gupta
  • 1