Hi,
I would like to calculate time difference between two timestamps(datetime) during business hours only(8:00AM to 6:00PM), excluding public holidays and week ends. public holidays list is saved in a dataset or as a list in a macro variable value.
for example, starting time is 30DEC2014:15:30:00 and ending time is 05JAN2015:09:52:00.
In this case 01Jan2015 is public holiday and 3rd & 4th Jan15 are week end. Therefore, time difference I want to calculate is
2:30(on 30th Dec14) + 10:00(on 31st Dec14) + 10:00(on 2nd Jan15) + 1:52(on 5th Jan15) =24:22
However, starting and/or ending time may be earlier than 8:00AM and/or later than 6:00PM. Still, I am only interested in time duration
during business hours.
Can any one help me to implement an efficient logic for the time calculation please.
Many thanks inadvance,
Hari
Hi,
Here is a way, its a bit messy and not optimised, but its only to show you a method. First, I wouldn't recommend putting the holidays in a macro parameter, that will just cause you headaches. I would expand the time by dates first, then remove holidays. Then have a datastep with a calculation of time, and retain that count through the dataset ending up with last row having total. As mentioned this was done quickly to give the idea.
data phols;
format hol_date date9.;
hol_date="01JAN2015"d; output;
run;
/* Expand dates */
data inter;
format start_dt end_dt datetime. datepart date9.;
start_dt="30dec2014T15:30:00"dt;
end_dt="05JAN2015T09:52:00"dt;
do i=datepart(start_dt) to datepart(end_dt);
datepart=i;
output;
end;
run;
/* Remove holidays */
proc sql;
delete from INTER where exists(select HOL_DATE from PHOLS where HOL_DATE=DATEPART);
quit;
data want;
set inter end=last;
format tot_time time5.;
retain tot_time;
if _n_=1 then do; /* These are where times come in */
if timepart(start_dt) < "08:00"t then tot_time="10:00"t;
else tot_time="18:00"t - timepart(start_dt);
end;
else if last then do;
if timepart(end_dt) > "18:00"t then tot_time=tot_time+"10:00"t;
else tot_time=tot_time + ("10:00"t - ((timepart(end_dt) - "08:00"t)));
/* output;*/
end;
else tot_time=tot_time+"10:00"t;
run;
Sounds like a "Custom Time Interval" is what you need, perhaps you can use the "Holiday" function to help you determine other days to exclude.
data holidays; input date : date9.; format date date9.; cards; 01Jan2015 ; run; data _null_; if _n_ eq 1 then do; declare hash h(dataset:'holidays'); h.definekey('date'); h.definedone(); end; time=-1; do i='30DEC2014:15:30:00'dt to '05JAN2015:09:52:00'dt ; date=datepart(i); if h.check() ne 0 and weekday(date) not in (1 7) then do; if 8 le hour(i) lt 18 then time+1; end; end; putlog time time.; run;
Xia Keshan
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.