topic Calculate and split working minutes between two dates in SAS Procedures

Calculate and split working minutes between two dates

mjheever — Tue, 23 Jun 2015 12:44:53 GMT

Hi everyone,

I have an start datetime and an end datetime, lets say 11JUN2015:03:14:02 and 16JUN2015:09:05:20 respectively.

I want to calculate the working minutes between these two datetimes, but I want the working minutes to be split into two groups. Working minutes is just calculated as (start datetime - end datetime)/60 .

The problem I'm getting is to split the working minutes into weekend working minutes and business day working minutes.

So what I want to get at is two variables that display the weekend working minutes and the business day working minutes respectively between the two datetimes.

For example:

input

start datetime: 11JUN2015:03:14:02

end datetime: 16JUN2015:09:05:20

Results

weekend working minutes = 2685 (Minutes)

business day working minutes = 2685 + 1985 = 4670 (Minutes)

Any ideas ? :smileygrin:

Message was edited by: Marnitz van den Heever.

Changes made are underlined.

Re: Calculate and split working hours between two dates

ballardw — Tue, 23 Jun 2015 14:45:22 GMT

1) You have a DATETIME, not a date. Pardon my yelling but I'm getting kind of frustrated with people calling datetimes as dates.

First: Define business day. Are holidays working days such as New Years, Christmas, Easter, etc?

How are holidays treated that fall on holidays?

How long are the intervals likely to be for these start end pairs?

If your start and end dates are SAS datetime variables then hours will not be

(start date-end date)/60 .

since SAS datetimes represent seconds the divisor would be 3600. BUT using SAS datetime variables you have functions to determine intervals as well as day of week, month, year of the variables.

Re: Calculate and split working hours between two dates

mjheever — Tue, 23 Jun 2015 15:35:18 GMT

Thank you for correcting me , I hope the problem is now fixed after I made changes to the original message.

Business day definition: Monday,Tuesday,Wednesday, Thursday, Friday (00h00 - 23h59).

Weekend day definition: Saturday, Sunday (00h00-23h59).

No need to take holidays into account.

The intervals vary from few minutes to days.

My understanding was that (start datetime - end datetime)/60 = minutes, am I understanding this wrong ?

Yes I have tried something similar to this thread: but it then generates a massive data set which takes up a lot of processing time.

Thank you for all the input Ballardw.

Re: Calculate and split working hours between two dates

ballardw — Tue, 23 Jun 2015 15:56:09 GMT

The division by 60 will give minutes

This is one very inefficient way but works by counting which category each second belongs to. Then divide to get seconds.

My example gets a different result than your example probably due to a rounding difference somewhere or possibly how you treated values near midnight.

data junk;
   start = '11JUN2015:03:14:02'dt;
   end   = '16JUN2015:09:05:20'dt;
   weekend=0;
   business=0;
   do i=start to end;
      if weekday(datepart(i)) in (1,7) then weekend+1;
      else business+1;
   end;
   weekend= weekend/60;
   business= business/60;
   drop i;
run;

Re: Calculate and split working hours between two dates

mjheever — Wed, 24 Jun 2015 10:28:32 GMT

Thank you Ballardw !

Your solution worked perfectly.

It takes a while to run on a relatively data set but if you got some processing time on your hands then the solution you provided works very well .

Re: Calculate and split working hours between two dates

ballardw — Wed, 24 Jun 2015 13:43:03 GMT

The exercise for the interested reader is:

Start with the starttime, see if the next midnight (intnx function) is in the same as the current period and accumulate the time. If the end time occurs before the next midnight then accumulate the time for the current. Repeat with the comparison at the next midnight. I think this should take an addition 12-15 lines of code but I was lazy!

Re: Calculate and split working hours between two dates

mjheever — Thu, 25 Jun 2015 11:30:14 GMT

Haha i'm definitely going to give it a try as you mentioned above Ballardw :smileygrin: