Hi,
My data looks like this (it was already sorted by group id and then by date):
data dat;
format ID 5. DATE YYMMDD10.;
input ID DATE : YYMMDD10.;
datalines;
1 2014-01-19
1 2014-05-18
2 2013-08-25
2 2014-01-19
2 2014-03-03
3 2016-03-19
3 2016-03-22
3 2016-04-21
4 2014-05-05
4 2014-08-06
5 2015-10-06
;
run;
I want to calculate the date difference by group, like this:
data want;
format ID 5. DATE YYMMDD10. INTVL 5.;
input ID DATE : YYMMDD10. intvl;
datalines;
1 2014-01-19 0
1 2014-05-18 119
2 2013-08-25 0
2 2014-01-19 147
2 2014-03-03 43
3 2016-03-19 0
3 2016-03-22 3
3 2016-04-21 30
4 2014-05-05 0
4 2014-08-06 93
5 2015-10-06 0
;
run;
I tried using the codes below but the output is wrong. Any suggestions?
data want2;
set dat;
by ID;
if first.ID then INTVL = 0;
else INTVL = intck('day', lag(DATE), DATE);
run;
please try the below code
data want;
set dat;
by id ;
INTVL = intck('day', lag(DATE), DATE);
if first.ID then INTVL = 0;
run;
please try the below code
data want;
set dat;
by id ;
INTVL = intck('day', lag(DATE), DATE);
if first.ID then INTVL = 0;
run;
Checking to see how retain works
data dat;
format ID 5. DATE YYMMDD10.;
input ID DATE : YYMMDD10.;
datalines;
1 2014-01-19
1 2014-05-18
2 2013-08-25
2 2014-01-19
2 2014-03-03
3 2016-03-19
3 2016-03-22
3 2016-04-21
4 2014-05-05
4 2014-08-06
5 2015-10-06
;
run;
data want;
do until(last.id);
set dat;
by id;
INTVL =intck('day',_iorc_,date)*(not first.id);
_iorc_=date;
output;
end;
run;
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 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.