Compute the difference between dates group by ID

John4 — Tue, 02 May 2017 09:56:20 GMT

I have a large data set like this :

ID NAME BEGIN END

191080    John             25/04/2016    01/05/2016
191080    John         08/06/2016   10/06/2016
191080    John         21/06/2016   24/06/2016
191141    Alex         16/01/2017   17/01/2017

191141 Alex 22/02/2017 29/04/2017

For each first second row, I want to compute the difference (I mean get the number of days) between BEGIN and END by ID

For exemple for John :

08/06/2016 - 01/05/2016 = 38

21/06/2016 - 10/06/2016 = 11

and for Alex : 22/02/2017 - 17/01/2017 = 36

I mean how get this :

ID NAME BEGIN END RESULT

191080    John             25/04/2016    01/05/2016
191080    John         08/06/2016   10/06/2016           38
191080    John         21/06/2016   24/06/2016           11
191141    Alex         16/01/2017   17/01/2017

191141 Alex 22/02/2017 29/04/2017 36

Re: Compute the difference between dates group by ID

PeterClemmensen — Tue, 02 May 2017 10:53:04 GMT

You can do it like this

data have;
input ID $ NAME $ BEGIN END;
informat BEGIN END DDMMYY10.;
format BEGIN END DDMMYY10.;
datalines; 
191080 John 25/04/2016 01/05/2016
191080 John 08/06/2016 10/06/2016
191080 John 21/06/2016 24/06/2016
191141 Alex 16/01/2017 17/01/2017
191141 Alex 22/02/2017 29/04/2017
;

proc sort data = have;
	by ID;
run;

data want;
	set have;
	
	by ID;
	lag_END = lag1(END);
	if not first.ID then RESULT = abs(intck('day', BEGIN, lag_END));

	drop lag_end;
run;

NOTE: Just edited the above, didn't realize about the whole lag-thing 🙂

topic Re: Compute the difference between dates group by ID in SAS Programming

Compute the difference between dates group by ID

Re: Compute the difference between dates group by ID