turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Calculate and split working minutes between two da...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-23-2015 08:44 AM

Hi everyone,

I have an* start datetime* and an

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

So what I want to get at is two variables that display the * weekend working minutes *and the

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mjheever

06-23-2015 11:56 AM

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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mjheever

06-23-2015 10:45 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

06-23-2015 11:35 AM

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* -

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mjheever

06-23-2015 11:56 AM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

06-24-2015 06:28 AM

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 .

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mjheever

06-24-2015 09:43 AM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

06-25-2015 07:30 AM

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