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
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
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
I used the business day function to substract 20 business days. This is a brilliant little piece of code. Thank You Haikuo
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
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.
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;
There is a holiday() function to check holiday and week() to check weekend.
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
*/
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 .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.