intervalds not working in macro

[danteploy]

I have the following code for generating date intervals. 

options intervalds=(BankingDays=work.BankDayDS);
data BankDayDS (keep=BEGIN );
start = '01DEC2018'D;
stop = '01JAN2030'D;
nwkdays = INTCK('WEEKDAY',start,stop);
do i = 0 to nwkdays;
BEGIN = INTNX('WEEKDAY',start,i);
BEGIN_PERIOD = INTNX('WEEKDAY',start,i);
year = YEAR(BEGIN);
if BEGIN ne HOLIDAY('NEWYEAR',year) and
BEGIN ne HOLIDAY('MLK',year) and
BEGIN ne HOLIDAY('USPRESIDENTS',year) and
BEGIN ne HOLIDAY('MEMORIAL',year) and
BEGIN ne HOLIDAY('USINDEPENDENCE',year) and
BEGIN ne HOLIDAY('LABOR',year) and
BEGIN ne HOLIDAY('VETERANS',year) and
BEGIN ne HOLIDAY('THANKSGIVING',year) and
BEGIN ne HOLIDAY('CHRISTMAS',year) then
output;
end;
format BEGIN DATE.;
format BEGIN_PERIOD MONYY.;
run;

It works when I use it in intnx or intck in a data step.  But when I try to use it inside a macro, I get, "WARNING: Argument 1 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range."

options minoperator;
%macro velocity()/mindelimiter=',';

/*** Cycle through week one workday at a time ***/
%let first_date = %sysfunc(inputn(01JAN2019,date9.));
/*%sysfunc(intnx(month, %sysfunc(today()), -1,b)); */
%let end_date = %sysfunc(inputn(01APR2019,date9.));
%let i = 1;

%put first_date = &first_date.;

%do %while(&first_date. < &end_date.);

%put first_date = &first_date;
%let year1 = %sysfunc(intnx(BankingDays, &first_date, -4), year.);
%let year2 = %sysfunc(intnx(BankingDays, &first_date, 0), year.);

Is there another option I need to use to get this to work?

[Astounding]

No, there's no option to get it to work.  In macro language, this is a string of 11 characters:  BankingDays

It does not refer to a variable.  It's just a set of letters.  You would need to find its actual value (assuming there is just one and not a set of values), and transform it into a number representing the proper date.

What advantage would you get from converting your code to macro language?

[ballardw]

---

@danteploy wrote:

I have the following code for generating date intervals. 

options intervalds=(BankingDays=work.BankDayDS);
data BankDayDS (keep=BEGIN );
start = '01DEC2018'D;
stop = '01JAN2030'D;
nwkdays = INTCK('WEEKDAY',start,stop);
do i = 0 to nwkdays;
BEGIN = INTNX('WEEKDAY',start,i);
BEGIN_PERIOD = INTNX('WEEKDAY',start,i);
year = YEAR(BEGIN);
if BEGIN ne HOLIDAY('NEWYEAR',year) and
BEGIN ne HOLIDAY('MLK',year) and
BEGIN ne HOLIDAY('USPRESIDENTS',year) and
BEGIN ne HOLIDAY('MEMORIAL',year) and
BEGIN ne HOLIDAY('USINDEPENDENCE',year) and
BEGIN ne HOLIDAY('LABOR',year) and
BEGIN ne HOLIDAY('VETERANS',year) and
BEGIN ne HOLIDAY('THANKSGIVING',year) and
BEGIN ne HOLIDAY('CHRISTMAS',year) then
output;
end;
format BEGIN DATE.;
format BEGIN_PERIOD MONYY.;
run;

It works when I use it in intnx or intck in a data step.  But when I try to use it inside a macro, I get, "WARNING: Argument 1 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range."

options minoperator;
%macro velocity()/mindelimiter=',';

/*** Cycle through week one workday at a time ***/
%let first_date = %sysfunc(inputn(01JAN2019,date9.));
/*%sysfunc(intnx(month, %sysfunc(today()), -1,b)); */
%let end_date = %sysfunc(inputn(01APR2019,date9.));
%let i = 1;

%put first_date = &first_date.;

%do %while(&first_date. < &end_date.);

%put first_date = &first_date;
%let year1 = %sysfunc(intnx(BankingDays, &first_date, -4), year.);
%let year2 = %sysfunc(intnx(BankingDays, &first_date, 0), year.);

Is there another option I need to use to get this to work?

---

Use a data step to create the macro variables? You don't show how you are using the macro variables so I can't tell if you actually need a completely macro solution.

data _null_;
   sd = '23JUN2020'd;
   nd = intnx('BankingDays',sd,4);
   call symputx('mvar',nd);
run;

%put mvar is: &mvar.;

[Reeza]

Post your exact log please.
You're setting the option before you actually create the data set. Try reversing those settings and see what happens. But....at the same time not all functions are valid within SYSFUNC. For example you cannot use PUT or INPUT so it's possible that a user defined interval may not valid.

[danteploy]

I tried changing the order of when I set the option, but get the same result.  Here's what the log says:

WARNING: Argument 1 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result of the operations have been set to a missing value.

[FreelanceReinh · Accepted solution]

I agree with @ballardw. And if you need to avoid step boundaries (e.g. for a function-style macro), DOSUBL comes to rescue.

Example:

%macro testf(first_date=);
%let rc=%sysfunc(%str(dosubl(data _null_;y=intnx('BankingDays', &first_date, -4);call symputx('year1', put(y,year.));run;)));
&year1
%mend testf;

%put %testf(first_date=21789);

Result: 2019

Custom intervals seem to be outside the scope of INTNX/INTCK when used via %SYSFUNC. Thanks for making us aware of this limitation.

[danteploy]

Thank you!