Dear All
My data is as follows
ID ID_A Date
1. A 03JAN2010
1. A. 04JAN2010
1. A. 10JAN2010
1. B. 05JAN2010
1. B. 07JAN2010
2. A. 02FEB2010
2. A. 08FEB2210
2. C. 18JAN2010
2 C. 20JAN2010
I want to construct a variable that calculates the Days between the dates
ID ID_A Date DBD_WO_Holidays.
1. A 03JAN2010 1
1. A. 04JAN2010 6
1. A. 10JAN2010. -50000
1. B. 05JAN2010. 2
1. B. 07JAN2010 -50000
2. A. 02FEB2010 6
2. A. 08FEB2210 -50000
2. C. 18JAN2010 2
2 C. 20JAN2010 -50000
The -50000 is just an indicator it is the last date for ID_A
Thanks in advance
Randy
You don't say what kind of holidays you have in mind. There're means in SAS to account for them (see the HOLIDAY function), but they have to be specific. With the holiday considerations aside, you just need to include a little mechanism of looking forward:
data have ;
input ID ID_A:$1. Date:date. ;
format date date9. ;
cards ;
1 A 03JAN2010
1 A 04JAN2010
1 A 10JAN2010
1 B 05JAN2010
1 B 07JAN2010
2 A 02FEB2010
2 A 08FEB2010
2 C 18JAN2010
2 C 20JAN2010
run ;
data want (drop = _:) ;
set have ;
by ID ID_A ;
if last.ID_A then DBD = -50000 ;
else do ;
p = _n_ + 1 ;
set have (keep=date rename=(date=_d)) point = p ;
DBD = intck ("day", date, _d) ;
end ;
run ;
HTH
Paul D.
p.s. Note that I had to correct your sample input DATE in record #7 from year 2210 to 2010, otherwise your sample output for the prior record makes no sense.
data have ;
input ID ID_A:$1. Date:date. ;
format date date9. ;
cards ;
1 A 03JAN2010
1 A 04JAN2010
1 A 10JAN2010
1 B 05JAN2010
1 B 07JAN2010
2 A 02FEB2010
2 A 08FEB2010
2 C 18JAN2010
2 C 20JAN2010
;
run ;
data want;
merge have have(rename=(id=_id id_a=_id_a date=_date) firstobs=2);
if id=_id and id_a=_id_a then DBD_WO_Holidays=_date-date;
else DBD_WO_Holidays=-50000;
drop _:;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.