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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.