topic Re: How To Add business days to a date column to get another date column in SAS Enterprise Guide

How To Add business days to a date column to get another date column

sharath_rk — Fri, 25 Apr 2014 15:45:04 GMT

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

Re: How To Add business days to a date column to get another date column

Haikuo — Fri, 25 Apr 2014 17:28:33 GMT

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

Re: How To Add business days to a date column to get another date column

sharath_rk — Sun, 27 Apr 2014 03:28:03 GMT

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

Re: How To Add business days to a date column to get another date column

Ksharp — Sun, 27 Apr 2014 04:22:51 GMT

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

Re: How To Add business days to a date column to get another date column

rajesh_rao — Wed, 07 May 2014 01:12:31 GMT

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

Re: How To Add business days to a date column to get another date column

ChrisHemedinger — Wed, 07 May 2014 13:06:01 GMT

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

Re: How To Add business days to a date column to get another date column

rajesh_rao — Thu, 08 May 2014 23:21:46 GMT

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.

Re: How To Add business days to a date column to get another date column

sharath_rk — Fri, 20 Jun 2014 14:32:02 GMT

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;

Re: How To Add business days to a date column to get another date column

AliceL — Wed, 27 Sep 2017 00:54:53 GMT

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&docsetVersion=14.2&locale=en 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

Re: How To Add business days to a date column to get another date column

LeonidBatkhan — Mon, 09 Mar 2020 01:19:49 GMT

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 .