topic time difference by group in SAS Programming

time difference by group

panda — Wed, 07 Aug 2019 16:01:58 GMT

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;

Re: time difference by group

Jagadishkatam — Wed, 07 Aug 2019 16:21:15 GMT

please try the below code

data want;
set dat;
by id ;
INTVL = intck('day', lag(DATE), DATE);
    if first.ID then INTVL = 0;
run;

Re: time difference by group

panda — Wed, 07 Aug 2019 16:32:46 GMT

Thanks @Jagadishkatam it worked!

Re: time difference by group

novinosrin — Wed, 07 Aug 2019 16:52:15 GMT

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;