Help using Base SAS procedures

Calculate and split working minutes between two dates

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Calculate and split working minutes between two dates

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.


Accepted Solutions
Solution
‎06-23-2015 11:56 AM
Super User
Posts: 10,514

Re: Calculate and split working hours between two dates

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;

View solution in original post


All Replies
Super User
Posts: 10,514

Re: Calculate and split working hours between two dates

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.

Contributor
Posts: 42

Re: Calculate and split working hours between two dates

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.

Solution
‎06-23-2015 11:56 AM
Super User
Posts: 10,514

Re: Calculate and split working hours between two dates

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;

Contributor
Posts: 42

Re: Calculate and split working hours between two dates

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 Smiley Happy.

Super User
Posts: 10,514

Re: Calculate and split working hours between two dates

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!

Contributor
Posts: 42

Re: Calculate and split working hours between two dates

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 578 views
  • 3 likes
  • 2 in conversation