BookmarkSubscribeRSS Feed
sharath_rk
Calcite | Level 5

Team,

I am needing to add business days to a date column ( Order_Date ) which should exclude weekends & holidays while adding the business days and the desired output should be date column. Please advise. In the below sample data, order_date is 02/22 (02/23 is weekend ) and 2 business days would be 2/25. Since 2/25 is a holiday, the output should be 02/26. The condition should exclude the weekend & holidays to give desired output. Holidays is a macro. Please assist.

Order_Date   Business Days     Ship_Date     Holidays     Desired Output

2/22/2014          2                    3/5/2014     2/25/2014     2/26/2014

2/24/2014         10                    3/8/2014     2/27/2014     3/13/2014

2/26/2014         15                    3/10/2014     3/4/2014     3/24/2013

2/28/2014         20                    3/12/2014     3/7/2014     4/1/2014

9 REPLIES 9
Haikuo
Onyx | Level 15

Chances are you will have to create your own customized shifting intervals. For details, please refer: SAS(R) 9.3 Functions and CALL Routines: Reference

The following snippet only has 2014.

options intervalds=(BUSINESSDAYS=BSDAY);

%LET HOLIDAY='25FEB2014'D '27FEB2014'D '04MAR2014'D '07MAR2014'D;

data BSDAY(keep=begin);

start = '01JAN2014'd;

stop  = '31DEC2014'd;

nwkdays = intck('weekday',start,stop);

do i = 0 to nwkdays;

      begin = intnx('weekday',start,i);

      year = year(begin);

      if begin NOT IN (&HOLIDAY.) then

      output;

end;

format begin date9.;

run;

DATA WANT;

INPUT Order_Date :MMDDYY10.   Business_Days    Desired_Output :MMDDYY10.;

NEW_OUTPUT_DATE=INTNX('BUSINESSDAYS',ORDER_DATE,Business_Days);

FORMAT ORDER_DATE Desired_Output NEW_OUTPUT_DATE MMDDYY10.;

CARDS;

2/22/2014          2                     2/26/2014

2/24/2014         10                     3/13/2014

2/26/2014         15                     3/24/2013

2/28/2014         20                     4/1/2014

;

Good Luck,

Haikuo

sharath_rk
Calcite | Level 5

Hai Kuo,

Thank you for providing the code. i am getting business days excluding weekends/holidays in the data BSDAY however i am not clear on the code for DATA WANT as to how do i reference the actual data set(ABC) which has multiple rows & columns to add BUSINESSDAYS to ORDER_DATE. The dataset (ABC) has the columns ORDER_DATE & Business_Days along with other columns. How do i provide the data for Input using the actual columns in the dataset (ABC) instead of listing it out per below.

NEW_OUTPUT_DATE column in the dataset (ABC) should give the business days added to the order_date. Please advise.

CARDS;

2/22/2014          2                     2/26/2014

2/24/2014         10                     3/13/2014

2/26/2014         15                     3/24/2013

2/28/2014         20                     4/1/2014

rajesh_rao
Calcite | Level 5

I used the business day function to substract 20 business days.  This is a brilliant little piece of code. Thank You Haikuo

ChrisHemedinger
Community Manager

Another approach is to use FCMP to create an equivalent of the NETWORKDAYS function you can find in Excel.  The function and explanation are provided here:

Calculating the number of working days between two dates - The SAS Dummy

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
rajesh_rao
Calcite | Level 5

The intervalds option doesn't seem to work unless the option and the code to get the businessdays function is just above the dataset where the funciton is used.  my question is, where should be option be specified so that it works globally across the code. 

sharath_rk
Calcite | Level 5

Thank you. I found the solution using the below.

A = B;

datecount =0;
do until(datecount > Business days );
     ECR=ECR+1;
     if weekday(A) not in (1,7) AND A not in (&AMERHOL.) then datecount=datecount+1;
end;

END;

Ksharp
Super User

There is a holiday() function to check holiday and week() to check weekend.

AliceL
Calcite | Level 5

Hi - On  top of the suggestions provided by others here and the help from Andy in SAS support team, I finally find the below solution works.

 

 

/* Initialisation - Begin

*/

/*EXAMPLE:

NEW_DATE = AddBizDays(S_Date $, E_Date $, Start_Date $, Num_Biz_Days $);

It's built from NEW_DATE = INTNX('BUSINESSDAYS',START_DATE,NUM_BIZ_DAYS)

*/

 

 

%let path = C:\00.ReferenceData;

%let Holiday_List_File = &path.\Australia Holidays.xls;

%let HOliday_List_Tab =NSW Holidays;

 

 

OPTIONS MSTORED SASMSTORE=StrMacAL;

LIBNAME StrMacAL '\\int\groupdata\TMF\Portfolio Management\Automations\$SAS Macros\Compiled Macro';

 

%MACRO CrtBizDays(Holiday_File=, Holiday_Tab=, S_DATE=, E_DATE=) / STORE SOURCE DES='This Macro create biz date dataset with given start and end dates';

 

PROC IMPORT OUT= WORK.AU_holidays (WHERE=(Holidays >= &S_Date. AND Holidays <=&E_Date.))

            DATAFILE= "&Holiday_File." 

            DBMS=EXCEL REPLACE;

     SHEET="&Holiday_Tab.$";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

 

PROC SQL;

SELECT DISTINCT

MIN(HOLIDAYS) 

,   MAX(HOLIDAYS) into :MIN_Holiday, :MAX_Holiday

FROM WORK.AU_holidays

;

QUIT;

 

          %if &E_Date. < &MIN_Holiday. or &S_Date. > &MAX_Holiday 

%THEN

                   %DO;

              %PUT "The input date range is not covered in the holiday list!";                                      

%END;

%ELSE;

%DO;

Proc sql;

Delete from WORK.AU_holidays

WHERE Holidays < &S_Date. 

  AND Holidays > &E_Date. 

;

    Quit;

%END;

 

 

 

 

/*        2.        Insert a row number: */

 

Data WORK.AU_HOLIDAYS;

Set        WORK.AU_holidays;

/*                                format holidays best.;*/

Rownum=_n_;

Run;

 

 

/*        3.        Get the # records of the list by SELECT INTO :NUM_HOLIDAYS*/

PROC SQL;

SELECT COUNT(*) INTO :NUM_HOLIDAYS

FROM WORK.AU_HOLIDAYS

;

QUIT;

%PUT &NUM_HOLIDAYS.;

 

/*        4.  Loop into concatenate all the holidays into a constant &HOLIDAYS.*/

 

 

/*                                                */

%LET HOLIDAYS=;        

 

%macro luckyLoop();

%LET i=1;        

 

/*        %LET Q1=';*/

/*        %LET Q2='D;*/

 

%do %while (&i.<= &NUM_HOLIDAYS.);

proc sql;

SELECT HOLIDAYS INTO :CUR_HOLIDAY

FROM WORK.AU_HOLIDAYS

WHERE RowNum = &i.;

;

%PUT &CUR_HOLIDAY.;

/*                                                        %LET HOLIDAYS =  &HOLIDAYS. &CUR_HOLIDAY.;*/

%LET HOLIDAYS =  &HOLIDAYS. %STR(%'&CUR_HOLIDAY.%'D);

%let i = %EVAL(&i.+1);

quit;

%end;

%put &HOLIDAYS.;

 

%mend luckyLoop;

 

%luckyLoop();

 

 

%put &HOLIDAYS.;

 

%put ##&HOLIDAYS.##;

 

/*        5.  Create biz day data set         */

 

options intervalds=(BUSINESSDAYS=BSDAY);

data BSDAY(keep=begin);

 

start = &S_Date.;

stop  = &E_Date.;

 

nwkdays = intck('weekday',start,stop);

do i = 0 to nwkdays;

 

      begin = intnx('weekday',start,i);

 

      year = year(begin);

 

/*                              if begin NOT IN (&HOLIDAYS.) then*/

  if begin NOT IN (%unquote(&HOLIDAYS.)) then

 

      output;

 

end;

format begin date9.;

run;

 

%MEND CrtBizDays;

 

%CrtBizDays(Holiday_File=&Holiday_List_File., Holiday_Tab=&Holiday_List_Tab., S_DATE='01JAN2017'D, E_DATE='31DEC2017'D);

/* Initialisation - End */

 

proc fcmp  outlib=work.myfuncs.dates;

  function AddBizDays(Start_Date, Num_Biz_Days);

 

 

  /*Format - Format the original  date so we can spot day of week easily */

  format Start_Date TEST_START_DATE TEST_START_DATE_new NEW_DATE weekdate.;

          

 

  TEST_START_DATE = INTNX('BUSINESSDAYS',Start_Date, 0 );

  TEST_START_DATE_new = INTNX('BUSINESSDAYS',Start_Date, 1 );

 

  If INTNX('BUSINESSDAYS',Start_Date, 0 ) NE Start_Date AND Num_Biz_Days <0

      then do;

    put 'WARNING: ' Start_Date ' is not a business day!' 'INTNX('BUSINESSDAYS',Start_Date, 0 ) is: ' TEST_START_DATE '. The INTNX('BUSINESSDAYS',Start_Date, 1 ) is:' TEST_START_DATE_new ;

          NEW_DATE=INTNX('BUSINESSDAYS',INTNX('BUSINESSDAYS',Start_Date,1),Num_Biz_Days);

  end;

  else do;

   new_date=INTNX('BUSINESSDAYS',Start_Date,Num_Biz_Days );

  end;

  return(NEW_DATE );

          ENDSUB;

run; quit;

 

options cmplib=work.myfuncs;

 

 

 

DATA TEST;

INPUT Start_Date :DATE9.   Business_Days    Desired_Output :DATE9. ;

 

NEW_OUTPUT_DATE = AddBizDays(Start_Date,Business_Days);

FORMAT Start_Date Desired_Output NEW_OUTPUT_DATE WeekDate. ;

 

CARDS;

 

/*Specifies that data lines follow*/

 

29Jun2017   -2    27Jun2017

30Jun2017   -2          28Jun2017

01Jul2017   -2          29Jun2017

02Jul2017   -2    29Jun2017

30Jun2017    2          04Jul2017

01Jul2017    2    04Jul2017

02Jul2017    2    04Jul2017

03Jul2017    2    05Jul2017

;

 

RUN;

proc sql;

delete from work.test where start_date is null;

quit;

 

      

 

proc print data= Test; run;

/* ----------Result------------------

Obs Start_Date Business_Days Desired_Output NEW_OUTPUT_DATE

2 Thursday, June 29, 2017 -2 Tuesday, June 27, 2017 Tuesday, June 27, 2017

3 Friday, June 30, 2017 -2 Wednesday, June 28, 2017 Wednesday, June 28, 2017

4 Saturday, July 1, 2017 -2 Thursday, June 29, 2017 Thursday, June 29, 2017

5 Sunday, July 2, 2017 -2 Thursday, June 29, 2017 Thursday, June 29, 2017

6 Friday, June 30, 2017 2 Tuesday, July 4, 2017 Tuesday, July 4, 2017

7 Saturday, July 1, 2017 2 Tuesday, July 4, 2017 Tuesday, July 4, 2017

8 Sunday, July 2, 2017 2 Tuesday, July 4, 2017 Tuesday, July 4, 2017

9 Monday, July 3, 2017 2 Wednesday, July 5, 2017 Wednesday, July 5, 2017

 

   ----------Result------------------ */

 

/*

Hi Alice,

The documentation at

http://go.documentation.sas.com/?docsetId=etsug&docsetTarget=etsug_intervals_sect008.htm&docsetVersi... says "If the END variable is not present, then the implied value of END at each observation is one less than the value of BEGIN at the next observation."

 

Where you do specify the End value as the same as the beginning value, you then cannot do calculations based on values not included in your custom interval.

 

My suggestion is to test for a negative number of calculations and then based on that then test also for whether the start_date is a business day, by using intnx with an increment of 0. That will return the beginning of the interval. This means that since 1st July is interpreted as being in the same interval as 30th June, we will see a difference between INTNX('BUSINESSDAYS','01Jul2017'd,0) and '01Jul2017'd.

 

I would look to implement something like the below, to find the use the next business day as the argument, when the increment you want is negative.

 

If this isn't clear, do give me a call to discuss.

 

Kind regards,

Andy

SAS Technical Support  Ph AU: +61 2 9428 0444

Ph NZ: 0508 275 727

W: www.sas.com

*/

LeonidBatkhan
Lapis Lazuli | Level 10

In my recent blog post Shifting a date by a given number of workdays  I address this problem using SAS user-defined format dayoff. and user-defined function shiftwd(). The approach takes into account business days excluding weekends and holidays, and it is not specific for any country or jurisdiction.

 

Using this function, adding (or subtracting) business days to a date will look like this:

 

data EVENTS_WITH_SHIFTS;
   set EVENTS;
   BEFORE_DATE = shiftwd(EVENT_DATE,-10); /* Decrement EVENT_DATE by 10 workdays */ 
   AFTER_DATE  = shiftwd(EVENT_DATE, 12); /* Increment EVENT_DATE by 12 workdays */ 
   format BEFORE_DATE AFTER_DATE date9.;
run;

 

There is also a SAS macro solution described there:

data EVENTS_WITH_SHIFTS;
   set EVENTS;
   %shiftwd(fromvar=EVENT_DATE,endvar=BEFORE_DATE,wdays=10,sign=-); /* Decrement EVENT_DATE by 10 workdays */ 
   %shiftwd(fromvar=EVENT_DATE,endvar=AFTER_DATE, wdays=12,sign=+); /* Increment EVENT_DATE by 12 workdays */ 
   format BEFORE_DATE AFTER_DATE date9.;
run

 See the blog post for more details: Shifting a date by a given number of workdays .

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 9677 views
  • 2 likes
  • 7 in conversation