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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.