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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.