topic Re: previously and next available date in SAS Procedures

previously and next available date

hdg — Tue, 10 Apr 2012 22:35:27 GMT

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!

Re: previously and next available date

Linlin — Tue, 10 Apr 2012 22:58:29 GMT

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

Re: previously and next available date

Haikuo — Wed, 11 Apr 2012 00:05:55 GMT

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

Re: previously and next available date

Haikuo — Wed, 11 Apr 2012 00:45:16 GMT

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

Re: previously and next available date

Ksharp — Wed, 11 Apr 2012 04:38:53 GMT

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