DATA Step, Macro, Functions and more

time difference between timestamps during business hours

Reply
New Contributor
Posts: 2

time difference between timestamps during business hours

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


Super User
Super User
Posts: 7,392

Re: time difference between timestamps during business hours

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;

Respected Advisor
Posts: 3,777

Re: time difference between timestamps during business hours

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.

Super User
Posts: 9,671

Re: time difference between timestamps during business hours

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

Ask a Question
Discussion stats
  • 3 replies
  • 345 views
  • 0 likes
  • 4 in conversation