Help using Base SAS procedures

previously and next available date

Reply
Frequent Contributor
Frequent Contributor
Posts: 79

previously and next available date

This is a really simple question. I have a dataset and am looking for the previously available date and the next available date in the dataset for example

This has to be grouped by var

Input

vardate
ann12/29/2000
ann1/1/2001
ann1/4/2001
joe9/10/2001
joe9/21/2001
joe

9/24/2001

Output should be

vardatelast available datenext available date
ann12/29/2000 12/29/2000
ann1/1/200112/9/20001/4/2001
ann1/4/20011/1/2001.
joe9/10/2001 9/21/2001
joe9/21/20019/10/20019/24/2001
joe9/24/20019/21/2001

Thanks for your help!

Super Contributor
Posts: 1,636

Re: previously and next available date

try this one:

data have;

input var $ date mmddyy10.;

cards;

ann 12/29/2000

ann 1/1/2001

ann 1/4/2001

joe 9/10/2001

joe 9/21/2001

joe 9/24/2001

;

data want;

set have;

by var;

set have ( firstobs = 2 keep = date rename = (date = Next_date) )

    have (      obs = 1 drop = _all_                            );

Prev_date = ifn( first.var, (.), lag(date) );

Next_date = ifn(  last.var, (.), Next_date );

format prev_date next_date date mmddyy10.;

run;

proc print;run;

               Obs    var          date     Next_date     Prev_date

                1     ann    12/29/2000    01/01/2001             .

                2     ann    01/01/2001    01/04/2001    12/29/2000

                3     ann    01/04/2001             .             01/01/2001

                4     joe    09/10/2001    09/21/2001             .

                5     joe    09/21/2001    09/24/2001    09/10/2001

                6     joe    09/24/2001             .            09/21/2001

Linlin

Respected Advisor
Posts: 3,156

Re: previously and next available date

LinLin has already provided classic 'look-back and look-ahead' data step solution. If you have ETS, then proc expand has its native approach:

data have;

input var $ date mmddyy10.;

format date mmddyy10.;

cards;

ann 12/29/2000

ann 1/1/2001

ann 1/4/2001

joe 9/10/2001

joe 9/21/2001

joe 9/24/2001

;

proc expand data=have out=want(drop=time) method=none;

      by var;

      convert date = lag_date   / transformout=(lag 1);

      convert date = lead_date  / transformout=(lead 1);

   run;

   proc print;run;

Haikuo

Respected Advisor
Posts: 3,156

Re: previously and next available date

In addition to LinLin's solution, and as part of classic 'look ahead and look back', there is SQL approach:

proc sql;

create table look_both_ways as

select lookback.*, next.date as Next_date        

  from  ( select have.*, prev.date as Prev_date        

           from have left join have as prev

             on have.var = prev.var and prev.date < have.date

           group by have.var, have.date

           having prev.date = max(prev.date)

        ) as lookback

   left join have as next

     on lookback.var = next.var and next.date > lookback.date

   group by lookback.var, lookback.date

   having next.date = min(next.date)

;

quit;

Haikuo

Super User
Posts: 10,018

Re: previously and next available date

data have;
input var $ date mmddyy10.;
format date mmddyy10.;
cards;
ann 12/29/2000
ann 1/1/2001
ann 1/4/2001
joe 9/10/2001
joe 9/21/2001
joe 9/24/2001
;
run;
data want(drop=_var);
 merge have have(firstobs=2 rename=(var=_var date=next_date));
 last_date=lag(date);
 if var ne _var then call missing(next_date);
 if var ne lag(var) then call missing(last_date);
 format last_date mmddyy10.;
run;


Ksharp

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