topic Re: intervalds not working in macro in SAS Programming

intervalds not working in macro

danteploy — Wed, 28 Aug 2019 14:04:16 GMT

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?

Re: intervalds not working in macro

Astounding — Wed, 28 Aug 2019 14:54:11 GMT

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?

Re: intervalds not working in macro

ballardw — Wed, 28 Aug 2019 15:26:50 GMT

@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.;

Re: intervalds not working in macro

Reeza — Wed, 28 Aug 2019 15:34:20 GMT

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.

Re: intervalds not working in macro

FreelanceReinh — Wed, 28 Aug 2019 15:41:22 GMT

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.

Re: intervalds not working in macro

danteploy — Wed, 28 Aug 2019 17:07:22 GMT

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.

Re: intervalds not working in macro

danteploy — Wed, 28 Aug 2019 17:17:49 GMT

Thank you!