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

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

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;

Grand Advisor
Posts: 9,584

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.

Ask a Question
Discussion stats
  • 7 replies
  • 3229 views
  • 2 likes
  • 5 in conversation