topic Re: Max date in SAS Programming

Max date

SASPhile — Thu, 10 Jan 2013 16:36:39 GMT

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;

Re: Max date

Astounding — Thu, 10 Jan 2013 17:05:32 GMT

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;

already_output='N';

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

output;

already_output='Y';

end;

retain already_output;

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.

Re: Max date

Reeza — Thu, 10 Jan 2013 17:27:14 GMT

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;

Re: Max date

asishgautam — Thu, 10 Jan 2013 17:41:01 GMT

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 ;

Re: Max date

asishgautam — Thu, 10 Jan 2013 17:43:49 GMT

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.