BookmarkSubscribeRSS Feed
harithalavu
Calcite | Level 5

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


3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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__
Jade | Level 19

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
Super User
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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2469 views
  • 0 likes
  • 4 in conversation