Dear All:
Could you please how to calculate the distance between days from the date the workshop first to a given date and the number of days to next date?
HAVE
Workshop-id Date
A 05/17/2012
A 05/27/2012
A 06/09/2012
B 11/04/2013
B 01/14/2014
C 10/10/2011
C 12/04/2011
C 01/24/2014
WANT
Workshop-id Date Difference
A 05/17/2012 10
A 05/27/2012 12
A 06/09/2012 0
B 11/04/2013 70
B 01/14/2014 0
C 10/10/2011 54
C 12/04/2011 30
C 01/04/2012 0
Best regards.
Try this:
proc sort data=have; by workshopId descending date; run;
data want;
set have; by workshopId;
prevDate = lag(date);
if first.workshopId then difference = 0;
else difference = intck("DAY", date, prevDate);
drop prevDate;
run;
proc sort data=want; by workshopId date; run;
PG
Try this:
proc sort data=have; by workshopId descending date; run;
data want;
set have; by workshopId;
prevDate = lag(date);
if first.workshopId then difference = 0;
else difference = intck("DAY", date, prevDate);
drop prevDate;
run;
proc sort data=want; by workshopId date; run;
PG
Dear PG,
Hardly thanks for you, _null and Hai.
Best regards.
Within the regime of data step, you could also try using look-ahead technique, but PG's code is the best for beginners to get their feet wet.
Below is to use Proc SQL as an alternative:
data have;
input Workshop_id:$ Date:mmddyy10.;
format date mmddyy10.;
cards;
A 05/17/2012
A 05/27/2012
A 06/09/2012
B 11/04/2013
B 01/14/2014
C 10/10/2011
C 12/04/2011
C 01/04/2012
;
proc sql;
select *, (select intck('day',a.date,b.date) from have b where a.workshop_id=b.workshop_id and a.date < b.date
group by b.workshop_id having b.date=min(b.date)) as Difference
from have a
;
quit;
Haikuo
coalesce((select....), 0) as Difference - to get the zeros.
Awesome, PG!
You can look ahead.
data workshop;
input id :$1. Date :mmddyy.;
format date mmddyy.;
cards;
A 05/17/2012
A 05/27/2012
A 06/09/2012
B 11/04/2013
B 01/14/2014
C 10/10/2011
C 12/04/2011
C 01/24/2014
;;;;
run;
data want;
merge workshop workshop(firstobs=2 rename=(id=_id date=_date));
difference=0;
if id=_id then difference=_date-date;
drop _:;
run;
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.