topic Re: time difference between timestamps during business hours in SAS Programming

time difference between timestamps during business hours

harithalavu — Tue, 17 Mar 2015 15:30:14 GMT

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

Re: time difference between timestamps during business hours

RW9 — Tue, 17 Mar 2015 15:59:01 GMT

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;

Re: time difference between timestamps during business hours

data_null__ — Tue, 17 Mar 2015 16:25:11 GMT

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.

Re: time difference between timestamps during business hours

Ksharp — Wed, 18 Mar 2015 14:05:24 GMT

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