topic I need assistance with developing a "Business Days/Hours" caclulation in SAS Programming

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

D_MichelleRenick — Fri, 23 May 2014 19:58:47 GMT

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!

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

Reeza — Fri, 23 May 2014 20:07:06 GMT

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

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

FriedEgg — Fri, 23 May 2014 23:51:07 GMT

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

, and I wrote a paper about this, which is available at the following link:

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;

*list of business holidays;

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;

stop;

run;

options intervalds=(MyBusiness=dates);

data foo;

set fake;

format new_approve datetime23.;

if sum(

intck('MyBusiness',dhms(datepart(approved),10,0,0),approved) ne 0,

intck('MyBusiness',dhms(datepart(approved),20,0,0),approved) ne 0

) ne 1 then new_approve=intnx('MyBusiness',approved,1,'b');

run;

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

JPM — Tue, 11 Nov 2014 20:09:59 GMT

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

      /* Match not found in master */
      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;
   CREATE TABLE WORK.Linked AS
   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
      FROM WORK.LINKED t1;
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;