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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.