BookmarkSubscribeRSS Feed
hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

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!

4 REPLIES 4
Linlin
Lapis Lazuli | Level 10

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
Onyx | Level 15

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
Onyx | Level 15

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
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 805 views
  • 0 likes
  • 4 in conversation