Obsidian | Level 7

## Compute the difference between dates group by ID

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: Compute the difference between dates group by ID

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 🙂

Tourmaline | Level 20

## Re: Compute the difference between dates group by ID

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 🙂

Discussion stats