topic Re: Calculating date differences with the minutes interval but excluding work days in New SAS User

Calculating date differences with the minutes interval but excluding work days

MBE57 — Wed, 03 Nov 2021 20:17:41 GMT

Hi, I need to calculate a turnaround time (tat) between two dates, to 2 decimal points, excluding work days. A team member helped me figure out the 1st part of the calculation, but we can't find how to exclude workdays at the minutes level. We'd appreciate some direction on this. Thank you.

What we have so far:

data TATcheck;

infile datalines dsd truncover;

format beg_date datetime16. end_date datetime16.;

input beg_date:datetime16. end_date:datetime16.;

datalines;

22MAR21:03:06:00,22MAR2021:18:16:00

17MAR21:13:23:00,17MAR2021:16:23:00

07APR21:12:29:00,07APR2021:17:34:00

12MAR21:16:55:08,15MAR2021:16:22:22

13MAR21:04:31:16, 15MAR2021:17:46:02

;

data tatcheck2;

set tatcheck;

format tat 6.2;

tat = ((intck('minute',beg_date,end_date))/60)/24;

run;

Results:

Beg_date End_date tat

22MAR21:03:06:00 22MAR21:18:16:00 0.63 (correct)

17MAR21:13:23:00 17MAR21:16:23:00 0.13 (correct)

07APR21:12:29:00 07APR21:17:34:00 0.21 (correct)

12MAR21:16:55:08 15MAR21:16:22:22 2.98 (needs to exclude weekend)

13MAR21:04:31:16 15MAR21:17:46:02 2.55 (needs to exclude weekend)

Re: Calculating date differences with the minutes interval but excluding work days

sbxkoenk — Wed, 03 Nov 2021 22:16:45 GMT

Hello,

Is the below program giving you what you want?

data TATcheck;
infile datalines dsd truncover;
format beg_date datetime16. end_date datetime16.;
 input beg_date:datetime16. end_date:datetime16.;
datalines;
22MAR21:03:06:00,22MAR2021:18:16:00
17MAR21:13:23:00,17MAR2021:16:23:00
07APR21:12:29:00,07APR2021:17:34:00
12MAR21:16:55:08,15MAR2021:16:22:22
13MAR21:04:31:16,15MAR2021:17:46:02
;
run;

data tatcheck3(drop=i);
 set tatcheck;
 format tat: 6.2;
 tat_exclude_weekend = 0;
 tat_exclude_workday = 0;
 do i = INTNX('dtminute',beg_date,0) to INTNX('dtminute',end_date,0) by 60;
  if weekday(datepart(i)) NOT IN (1,7)       then tat_exclude_weekend=tat_exclude_weekend+1;
  if weekday(datepart(i)) NOT IN (2,3,4,5,6) then tat_exclude_workday=tat_exclude_workday+1;
 end;
 tat_exclude_weekend = (tat_exclude_weekend/60)/24;
 tat_exclude_workday = (tat_exclude_workday/60)/24;
run;
/* end of program */

Koen

Re: Calculating date differences with the minutes interval but excluding work days

ballardw — Wed, 03 Nov 2021 22:18:22 GMT

First thing, clarify your actual need. The subject of the post says "excluding work days", the body of your question says "but we can't find how to exclude workdays at the minutes level." and then you example shows:

12MAR21:16:55:08 15MAR21:16:22:22 2.98 (needs to exclude weekend)

Two of these things are not like the other.

You also really don't mention what UNITS your result is supposed to be.

Details: Assuming a Friday to Monday interval, what time on FRIDAY to stop incrementing time? What time on MONDAY to you start incrementing the time?

And for an obnoxious kicker, what about Holidays? What do you expect to happen when a Holiday is in the interval?

The basic approach would be to test your two values to get the days of the week involved.

Dayofweek = weekday(datepart(beg_date));

Friday is 6, Monday is 2 for example. Then decide using that information if a weekend (week day???) is involved.

Then you get to apply the not-stated yet rule for how much time is accumulated for which days on the outside of your exclusion period needs to be counted. There is going to be some If/Then/else logic involved.

You would split things up such as

FirstBit = ((intck('minute',beg_date,<date time to stop for end of day friday>))60)/24;

Secondbit= ((intck('minute',<date time to start for monday>,end_date))60)/24;

Then add Firstbit and secondbit.

The INTNX function and/or DHMS would be in the second bit.

But without an example of the exact times involved I'm not going to write code.

Re: Calculating date differences with the minutes interval but excluding work days

Ksharp — Thu, 04 Nov 2021 11:44:51 GMT

data TATcheck;
infile datalines dsd truncover;
format beg_date datetime16. end_date datetime16.;
 input beg_date:datetime16. end_date:datetime16.;
datalines;
22MAR21:03:06:00,22MAR2021:18:16:00
17MAR21:13:23:00,17MAR2021:16:23:00
07APR21:12:29:00,07APR2021:17:34:00
12MAR21:16:55:08,15MAR2021:16:22:22
13MAR21:04:31:16,15MAR2021:17:46:02
;
run;
data want;
 set tatcheck;
 seconds=0;
 do i=beg_date to end_date;
   if weekday(datepart(i)) not in (1 7) then seconds+1;
 end;
 tat=seconds/60/60/24;
 drop seconds i;
run;

Re: Calculating date differences with the minutes interval but excluding work days

MBE57 — Thu, 04 Nov 2021 13:12:17 GMT

Thank you for your help, it's appreciated very much.