BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mjheever
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

6 REPLIES 6
ballardw
Super User

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.

mjheever
Obsidian | Level 7

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.

ballardw
Super User

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;

mjheever
Obsidian | Level 7

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.

ballardw
Super User

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!

mjheever
Obsidian | Level 7

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 2571 views
  • 3 likes
  • 2 in conversation