time difference between timestamps during business hours

[harithalavu]

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

[RW9]

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;

[data_null__]

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.

[Ksharp]

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