Compute the difference between dates group by ID

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Compute the difference between dates group by ID

[ Edited ]

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

 

 

 

 


Accepted Solutions
Solution
‎05-02-2017 06:19 AM
PROC Star
Posts: 547

Re: Compute the difference between dates group by ID

[ Edited ]

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 Smiley Happy 

View solution in original post


All Replies
Solution
‎05-02-2017 06:19 AM
PROC Star
Posts: 547

Re: Compute the difference between dates group by ID

[ Edited ]

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 Smiley Happy 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 105 views
  • 1 like
  • 2 in conversation