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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.