03-17-2015 11:30 AM
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,
03-17-2015 11:59 AM
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.
format hol_date date9.;
/* Expand dates */
format start_dt end_dt datetime. datepart date9.;
do i=datepart(start_dt) to datepart(end_dt);
/* Remove holidays */
delete from INTER where exists(select HOL_DATE from PHOLS where HOL_DATE=DATEPART);
set inter end=last;
format tot_time time5.;
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);
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)));
03-18-2015 10:05 AM
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;