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
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 🙂
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 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.