previously and next available date

[hdg]

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

var	date
ann	12/29/2000
ann	1/1/2001
ann	1/4/2001
joe	9/10/2001
joe	9/21/2001
joe	9/24/2001

Output should be

var	date	last available date	next available date
ann	12/29/2000		12/29/2000
ann	1/1/2001	12/9/2000	1/4/2001
ann	1/4/2001	1/1/2001	.
joe	9/10/2001		9/21/2001
joe	9/21/2001	9/10/2001	9/24/2001
joe	9/24/2001	9/21/2001

Thanks for your help!

[Linlin]

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

[Haikuo]

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

[Haikuo]

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

[Ksharp]

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