CODEWITHSUNDEEP
datesasretain
Keith
Keith

Reputation: 1

SAS 94 How to calculate the number of days until next record

Using SAS I want to be able to calculate the number of days between two dates where the value is the number of days until the next record.

The required output will be:

Date       Num Days
10/09/2020     1
11/09/2020     1
12/09/2020     1
14/09/2020     2
15/09/2020     1
16/09/2020     1
17/09/2020     1
18/09/2020     1
20/09/2020     2

I have tried using Lag and Retain but just cant get it work.

Any advice and suggestions would be really appreciated.

Upvotes: 0

Views: 173

Answers (1)

Tom
Tom

Reputation: 51566

If you sort the data by descending DATE then it is easier because then you just need to look backwards to find the next date. So you can use LAG() or DIF() function.

data want;
   set have;
   by descending date;
   num_days = dif(date);
run;

To simulate a "lead" function you can set another copy of the data skipping the first observation.

data want;
   set have ;
   set have(firstobs=2 keep=date rename=(date=next_date)) have(obs=1 drop=_all_);
   num_days = next_date - date;
run;

Upvotes: 0

Related Questions