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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.