Quartz | Level 8

## Max date

if id has date 0000-01-01 then output that record.
if not output the record for id with its max date

data tty;
input id dt :\$10.;
cards;
111 0001-01-01
111 2012-11-01
112 2012-11-01
112 2012-12-01
;
run;

4 REPLIES 4
PROC Star

## Re: Max date

Is efficiency an issue in any way?  If not, here is a straightforward way:

proc sort data=tty;

by id dt;

run;

data want;

set tty;

by id;

if last.id or dt='0001-01-01';

run;

data want;

set want;

by id;

if first.id;

run;

If efficiency is an issue, you can certainly combine the last two steps without getting too complex:

data want;

set tty;

by id;

if first.id then do;

if dt='0001-01-01' then do;

output;

end;

end;

if last.id and already_output='N' then output;

run;

These methods assume that there will be at most one record per ID with 0001-01-01 (or that if multiples exist, you only want one).

There are more complex, faster ways, but I would recommend sticking with code that makes sense to you.

Good luck.

Super User

## Re: Max date

data want;

set tty;

by id;

retain flag;

if first.id then flag=0;

if dt='0001-01-01' then do;

flag=1;

output;

end;

else if last.id and flag=0 then output;

run;

Calcite | Level 5

## Re: Max date

data tty;

format dt date9. ;

input id dt yymmdd10. ;

cards;

111 0001-01-01

111 2012-11-01

112 2012-11-01

112 2012-12-01

113 2000-01-01

113 2012-12-05

113 2011-12-05

;

run;

proc sort data = tty ;

by id dt ;

run ;

data tty_b /*(drop = flag_1)*/ ;

retain flag_1 ;

do until(last.id or flag_1 = 1) ;

set tty ;

by id ;

if first.id then flag_1 = 0 ;

if dt = '01JAN2000'd then do ;

flag_1 = 1 ;

output ;

end ;

if ((last.id) and (flag_1 = 0)) then output ;

end ;

run ;

Calcite | Level 5

## Re: Max date

nuts - just saw the reply from Reeza - both of our answers are very similar - I was trying to go through the dow loop and added a few more examples to data set to test things out.

Discussion stats
• 4 replies
• 967 views
• 4 likes
• 4 in conversation