Desktop productivity for business analysts and programmers

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

Reply
Contributor
Posts: 51

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

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

Respected Advisor
Posts: 3,156

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

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

Contributor
Posts: 51

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

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

New Contributor
Posts: 2

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

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

Community Manager
Posts: 2,882

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

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

New Contributor
Posts: 2

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

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. 

Contributor
Posts: 51

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

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;

Super User
Posts: 9,854

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

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

New User
Posts: 1

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

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 Smiley Very HappyATE9.   Business_Days    Desired_Output Smiley Very HappyATE9. ;

 

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

*/

Ask a Question
Discussion stats
  • 8 replies
  • 3598 views
  • 2 likes
  • 6 in conversation