I have a table that is sorted by a date column. In a new column (called interval), I want to be able to calculate the number of days between the date in the subsequent record. I feel like there has to be a simple way to do this, but I can't figure it out.
i.e. the first record would always be 0. Then it is 32 days from 6/17/2020 to 7/19/2020, and then 94 days from 7/19/2020 to 10/21/2020, etc.
data have;
input id latestdt :yymmdd10.;
format latestdt yymmddd10.;
datalines;
1 2020-06-17
2 2020-07-19
3 2020-10-21
4 2020-10-26
5 2020-11-05
6 2020-11-13
7 2020-11-13
;
run;
data want;
input id latestdt :yymmdd10. interval;
format latestdt yymmddd10.;
datalines;
1 2020-06-17 0
2 2020-07-19 32
3 2020-10-21 94
4 2020-10-26 5
5 2020-11-05 10
6 2020-11-13 8
7 2020-11-13 0
;
run;
data want;
set have;
interval=latestdt-lag(latestdt);
run;
@csanfor2 DIF function is rather straight forward i would think-
data have;
input id latestdt :yymmdd10.;
format latestdt yymmddd10.;
datalines;
1 2020-06-17
2 2020-07-19
3 2020-10-21
4 2020-10-26
5 2020-11-05
6 2020-11-13
7 2020-11-13
;
run;
data want;
set have;
interval=dif(latestdt);
if not interval then interval=0;
run;
April 27 – 30 | Gaylord Texan | Grapevine, Texas
Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and save with the early bird rate—just $795!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.