Not applicable
Posts: 1

# I need assistance with developing a "Business Days/Hours" caclulation

I’m trying to calculate a variance in time of approval where if the record was submitted during non-business hours (outside of 08:00am to 17:00pm) that it will move the start date of the calculation to the next business date at 08:00.  So for example, if the item was submitted for approval on Friday, May 23, 2014 at 5:30 pm (23MAY2014;17:30:00) it would show that the start date would be Tuesday, May 27th at 8am (27MAY2014;08:00:00) excluding the weekend and US holiday, and then in this example, the approval would be let’s say Tuesday at 4pm (27MAY2014;15:59:00) then the cycle time of this approval would be:  27MAY2014;15:59:00 minus 27MAY2014;08:00:00 == 7 hrs 59 minutes If the approval time moved to Wednesday at 10:00am, then the cycle time would have to again, go to business hours and be: 28MAY2014;10:00:00 minus 27MAY2014;08:00:00 == 11 hours (9 hours on the 27th plus 2 hours on the 28th) Any assistance with this would be greatly appreciated!

Super User
Posts: 23,683

## Re: I need assistance with developing a "Business Days/Hours" caclulation

Can you post some (more) sample data and expected output as it makes it easier to test code. Make sure to include some that have holidays included

Posts: 1,318

## Re: I need assistance with developing a "Business Days/Hours" caclulation

One way to handle a problem like this is the use the INTERVALDS option.

Sometimes One Needs an Option with Unusual Dates - sasCommunity

Here is an example code that I believe will get you what you need once modified to your specifications:

*some fake approval dates;

data fake;

format i 5. approved datetime23.;

call streaminit(313131);

do i=1 to 1e3;

approved='03MAY2014:00:00:00'dt + floor((1+'28MAY2014:24:59:59'dt-'01MAY2014:24:59:59'dt)*rand('uniform'));

output;

end;

run;

data holiday;

infile cards4;

input holiday \$;

do year=2014 to 2014;

x=holiday(holiday,year);

output;

end;

cards4;

MEMORIAL

;;;;

*custom date interval dataset;

data dates;

format begin end datetime23. x date9.;

keep begin end;

x=.; *init x variable;

declare hash hol(dataset:'holiday');

hol.definekey('x');

hol.definedone();

d='01MAY2014'd; *start date;

do until(d>'31MAY2014'd); *do until end date;

if hol.find(key:d) = 0. then do; *closed for holiday;

season=0.;

begin=dhms(d-1.,17.,0.,0.);

d+1.;

if weekday(d) = 7. then d+2.; *weekend following holiday;

end=dhms(d+1.,7.,59.,59.);

duration=round((end-begin)/60./60.,1.);

output;

end;

else if weekday(d) = 6. then do; *friday and weekend;

*open friday;

begin=dhms(d,8.,0.,0.);

end=dhms(d,16.,59.,59.);

output;

*closed weekend;

begin=dhms(d,17.,0.,0.);

d+3.;

if hol.find(key:d) = 0. then d+1.; *holiday following weekend;

end=dhms(d,7.,59.,59.);

output;

end;

else do; *regular day;

*open;

begin=dhms(d,8.,0.,0.);

end=dhms(d,16.,59.,59.);

output;

*close;

begin=dhms(d,17.,0.,0.);

d+1.;

end=dhms(d,7.,59.,59.);

output;

end;

end;

stop;

run;

data foo;

set fake;

format new_approve datetime23.;

if sum(

run;

New Contributor
Posts: 3

## Re: I need assistance with developing a "Business Days/Hours" caclulation

/*This uses the SAS "holiday and weekend" program, then checks your dates/times and fixes them to conform to your business rules */

/*It is just four simple queries once you get the holiday file made */

/*Runs in less than a second. */

/*Good luck on your project!  */

/*Create The Calendar File*/
/*Obtained directly from the SAS Knowledge Base see file http://support.sas.com/kb/26/044.html */

/***************************************************************************/
/* This sample creates three data sets, HOLIDAYS, WEEKENDS, and ALLDAYS    */
/* which are combined to create CALENDAR.  If you must often calculate the */
/* number of business days between dates, you might prefer to make         */
/* CALENDAR a permanent data set rather than recreate it each time.        */
/*                                                                         */
/* Two methods are provided below which use CALENDAR and a second data set */
/* with multiple start and end dates to determine the number of business   */
/* dates between STARTDT and STOPDT.                                       */
/***************************************************************************/

/* If you choose, you can edit your calendar ranges here.  No other edits  */
/* are needed to the steps creating the data set CALENDAR.                 */

%let start='01jan1960'd;
%let stop='01jan2060'd;

/* Create a date set for holidays.  Adjust to fit your company's needs.     */
/* Note logic is illustrated for 'static' holidays and 'observed' holidays. */

data holidays;
length type \$25;
do year=year(&start) to year(&stop);

/* example of 'observed' holiday logic  */
type='New Years Day Observed';
dt=MDY(1,1,YEAR);
FDOY=dt;
if weekday(dt)=1 then dt=dt+1;
else if weekday(dt)=7 then dt=mdy(12,31,year-1);
output;

/* example of static holiday logic */
type='Martin Luther King Day';
dt=intnx('week.2',fdoy,(weekday(fdoy) ne 2)+2);
output;

type="Presidents Day";
fdo_feb=intnx('month',fdoy,1);
dt=intnx('week.2',fdo_feb,(weekday(fdo_feb) ne 2)+2);
output;

type='Memorial Day';
fdo_may=intnx('month',fdoy,4);
dt=intnx('week.2',fdo_may,(weekday(fdo_may) in (1,7))+4);
output;

type='Independence Day Observed';
dt=MDY(7,4,YEAR);
if weekday(dt)=1 then dt=dt+1;
else if weekday(dt)=7 then dt=dt-1;
output;

type='Labor Day';
fdo_sep=intnx('month',fdoy,8);
dt=intnx('week.2',fdo_sep,(weekday(fdo_sep) ne 2));
output;

type='Election Day';
fdo_nov=intnx('month',fdoy,10);
dt=intnx('week.3',fdo_nov,1);
output;

type='Veterans Day Observed';
dt=MDY(11,11,YEAR);
if weekday(dt)=1 then dt=dt+1;
else if weekday(dt)=7 then dt=dt-1;
output;

type='Thanksgiving Day';
dt=intnx('week.5',fdo_nov,(weekday(fdo_nov) ne 5)+3);
output;

type='Christmas Day Observed';
dt=MDY(12,25,YEAR);
if weekday(dt)=1 then dt=dt+1;
else if weekday(dt)=7 then dt=dt-1;
output;
end;
keep dt type;
run;

proc sort data=holidays;
by dt;
run;

/* Create a data set of weekends via the WEEKDAY function  */

data weekends;
length type \$25;
type='Weekend';
do dt=&start to &stop;
if weekday(dt) in (1,7) then output;
end;
run;

/* Create a data set of all the days in the specified date range. */
/* TYPE will have the value 'Workday' for all observations.       */
/* Specifying ALLDAYS first in the following MERGE will allow any */
/* date matches from from HOLIDAY or WEEKEND to overwrite the     */
/* value of TYPE with the appropriate type of day.                */

data alldays;
length type \$25;
type='Workday';
do dt=&start to &stop;
output;
end;
run;

data calendar;
format dt date9.;
merge alldays(in=a) weekends(in=w) holidays(in=h);
by dt;
run;

/* Generate dummy data to use with CALENDAR for testing purposes */

data test;
startdt='01nov2014'd;  stopdt='30nov2014'd;
output;
startdt='01jan2014'd;  stopdt='10jul2014'd;
output;
startdt='20dec2014'd;  stopdt='15jan2015'd;
output;
format startdt stopdt date9.;
run;

/* Method 1:  SQL */

proc sql;
create table final_sql as
select startdt format=date9.,
stopdt format=date9.,
(select count(*)
from calendar
where dt between stopdt and startdt
and type = 'Workday') as workdays
from test;
quit;

proc print data=final_sql;
title 'Output from PROC SQL';
run;

/* Method 2:  DATA step using an INDEX and KEY=  */

/* Build index on CALENDAR */

proc datasets library=work nolist;
modify calendar;
index create dt;
quit;

data final_idx;
set test;
workdays=0;
/* For each date between STARTDT and STOPDT, check to see if DT is a workday. */
/* If so, increment the new variable WORKDAYS by 1.                           */
do i=startdt to stopdt;
dt=i;
/* Look up the current value of DT in CALENDAR using the index on DT */
set calendar key=dt/unique;
/* Check return code from search */
select (_iorc_);

/* Match found */
when (%sysrc(_sok)) do;
if type='Workday' then workdays+1;
if i=stopdt then output;
end;

when (%sysrc(_dsenom)) do;
_ERROR_=0;
end;

otherwise do;
put 'Unexpected ERROR: _iorc_= ' _iorc_;
stop;
end;
end;
end;
keep startdt stopdt workdays;
run;

proc print data=final_idx;
title "Output from DATA Step";
run;

proc datasets lib = work nolist;
save calendar;

/* Creating a sample file to mimic your specifications*/

data testvariance;
format example \$30.;
example = "1 - A Holiday";
format literal_receipt datetime.;
format literal_date date9.;
Literal_receipt="01jan2014 09:00:00"dt;
Literal_date=datepart(literal_receipt);
output;

example = "2 - A Weekend";
Literal_receipt="04jan2014 11:00:00"dt;
Literal_date=datepart(literal_receipt);
output;

example = "3 - Early Hours";
Literal_receipt="07jan2014 01:00:00"dt;
Literal_date=datepart(literal_receipt);
output;

example = "4 - Late Hours";
Literal_receipt="14jan2014 21:00:00"dt;
Literal_date=datepart(literal_receipt);
output;

example = "4 - Bump Weekend to Holiday";
Literal_receipt="25may2014 10:00:00"dt;
Literal_date=datepart(literal_receipt);
output;

example = "5 - Observed Xmas on Friday";
Literal_receipt="24dec1999 10:00:00"dt;
Literal_date=datepart(literal_receipt);
output;

run;
/* link test file to calendar file for type */
run;PROC SQL;
SELECT t1.example,
t1.literal_date,
t1.literal_receipt,
t2.dt,
t2.type
FROM WORK.TESTVARIANCE t1
INNER JOIN WORK.CALENDAR t2 ON (t1.literal_date = t2.dt);
QUIT;
/* shift according to type, or according to time received */
PROC SQL;
CREATE TABLE WORK.Reset_Receipt_DT AS
SELECT t1.dt,
t1.example,
t1.literal_date,
t1.literal_date format=weekdatx9. as DayOfWeek,
t1.literal_receipt,
t1.type,
/* Moved_Receipt_DT */
(ifn(t1.type<>"Workday" and t1.type<>"Weekend" and weekday(datepart(t1.literal_receipt))=6,intnx("dtday"
,t1.literal_receipt,2,"b")+(8*60*60),
ifn(t1.type<>"Workday" and t1.type<>"Weekend" and weekday(datepart(t1.literal_receipt)) in (2,3,4,5) ,intnx(
"dtday",t1.literal_receipt,1,"b")+(8*60*60),
ifn(timepart(t1.Literal_receipt)<(8*60*60), intnx("dtday", t1.literal_receipt,0,"b")+(8*60*60),
ifn(timepart(t1.Literal_receipt)>(17*60*60),intnx("dtday",t1.literal_receipt,1,"b")+(8*60*60),
ifn(weekday(datepart(t1.Literal_receipt))=7,intnx("dtday",t1.literal_receipt,2,"b")+(8*60*60),
ifn(weekday(datepart(t1.Literal_receipt))=1,intnx("dtday",t1.literal_receipt,1,"b")+(8*60*60),t1.literal_receipt)))))))  format = datetime. AS Moved_Receipt_DT,
datepart(calculated moved_receipt_dt) format=date9. as New_date
QUIT;

/* You have to relink to calendar in order to see if you moved a holiday onto a weekend, and so move again */
PROC SQL;
CREATE TABLE WORK.Reset_Receipt_DT AS
SELECT t1.DayOfWeek,
t1.dt,
t1.example,
t1.literal_date,
t1.literal_receipt,
t1.Moved_Receipt_DT,
t1.New_date,
t2.type
FROM WORK.RESET_RECEIPT_DT t1
INNER JOIN WORK.CALENDAR t2 ON (t1.New_date = t2.dt);
QUIT;
/*This is the final moved date/time */
PROC SQL;
CREATE TABLE WORK.Final_out AS
SELECT t1.dt,
t1.example,
t1.literal_date,
t1.DayOfWeek,
t1.literal_receipt,
t1.type,
/* Moved_Receipt_DT */
(ifn(t1.type<>"Workday" and t1.type<>"Weekend" and weekday(datepart(t1.Moved_Receipt_DT))=6,intnx("dtday"
,t1.Moved_Receipt_DT,2,"b")+(8*60*60),
ifn(t1.type<>"Workday" and t1.type<>"Weekend" and weekday(datepart(t1.Moved_Receipt_DT)) in (2,3,4,5) ,intnx(
"dtday",t1.Moved_Receipt_DT,1,"b")+(8*60*60),
ifn(timepart(t1.Moved_Receipt_DT)<(8*60*60), intnx("dtday", t1.Moved_Receipt_DT,0,"b")+(8*60*60),
ifn(timepart(t1.Moved_Receipt_DT)>(17*60*60),intnx("dtday",t1.Moved_Receipt_DT,1,"b")+(8*60*60),
ifn(weekday(datepart(t1.Moved_Receipt_DT))=7,intnx("dtday",t1.Moved_Receipt_DT,2,"b")+(8*60*60),
ifn(weekday(datepart(t1.Moved_Receipt_DT))=1,intnx("dtday",t1.Moved_Receipt_DT,1,"b")+(8*60*60),t1.Moved_Receipt_DT)))))))  format = datetime. AS Moved_Receipt_DT_2
FROM WORK.Reset_Receipt_DT t1;
QUIT;

Discussion stats
• 3 replies
• 494 views
• 0 likes
• 4 in conversation