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 */
... View more
