DATA Step, Macro, Functions and more

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

Reply
N/A
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: 17,867

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 Smiley Happy

Trusted Advisor
Posts: 1,300

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.

, 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;

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;

New Contributor JPM
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;

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

Ask a Question
Discussion stats
  • 3 replies
  • 356 views
  • 0 likes
  • 4 in conversation