DATA Step, Macro, Functions and more

Max date

Reply
Super Contributor
Posts: 673

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;

Super User
Posts: 5,498

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;

      already_output='N';

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

         output;

         already_output='Y';

      end;

   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.

Super User
Posts: 19,770

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;

Contributor
Posts: 66

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 ;

Contributor
Posts: 66

Re: Max date

Posted in reply to asishgautam

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.

Ask a Question
Discussion stats
  • 4 replies
  • 250 views
  • 4 likes
  • 4 in conversation