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!
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
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
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.