Error of intnx function in a macro variable

[viviwu]

Hello guys, I have a problem  about the usage of INTNX function in the MACRO, the codes are as following:

DATA TEST;
%let date_fin = '31OCT2017'd;
%let date_deb = intnx("month",&date_fin,-12,"sameday");
date = intnx(month, &date_deb., -12, sameday); 
format date date9.;
RUN;

The objective is : only need to change "date_fin" outside the Macro, in which "date" is calculated automatically with intnx function inside the "%macro blabla ... %mend" macro program

In works in this test data step. but when I put it in the macro program as

%let date_fin = '31OCT2017'd;
%let date_deb = intnx("month",&date_fin,-12,"sameday");

%macro test;
%let date = %sysfunc(intnx(month, &date_deb, -12, sameday));

...

%mend test;

And the error in log is as following:

ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC

Thank you for your kind help.

Vivi

[ChrisHemedinger]

That %let in the DATA step is not doing what you think it does -- it does not assign the value as part of the step -- rather, the macro processor handles that, and so requires proper syntax for SAS macros.  When using this function in macro code, you need to use %sysfunc and remove the quotes from the literal values.

%let date_deb = %sysfunc(
  intnx(
   month,             /* unit of time interval */
   &date_fin.
   -12,                /* number of intervals, negative goes to the past */
   same               /* alignment of interval date. "Same" is for same day of month */
   )
  );

See this blog post for more information.

[Reeza]

It's not sameday, I think it's 'SAME' for the last parameter to INTNX> 

You're also using quotes inconsistently within the formula and you shouldn't ahve %LET in the middle of a data step. In a data step use CALL SYMPUTX instead to create macro variables and it allows you to control the scope as well.

Here's an example:

DATA _null_;
date_fin = '31OCT2017'd;
date_deb = intnx('month',date_fin,-12,"same");
date = intnx('month', date_deb, -12, 'same'); 

call symputx('date_fin', date, 'g');
call symputx('date_deb', date_deb, 'g');

/*if you want them to look like date9*/
call symputx('date_fin2', put(date, date9.), 'g');
call symputx('date_deb2', put(date_deb, date9.) , 'g');

RUN;

%put &date_fin.;
%put &date_deb.;

%put &date_fin2.;
%put &date_deb2.;

[PaigeMiller]

You could make your life a lot simpler if you used data set variables instead of macro variables wherever possible. If &date_fin is created outside the data set then you could try something like this:

data test;
    date_fin=&date_fin;
    date_deb=intnx('month',date_fin,-12,'same');
    date = intnx('month',date_deb, -12,'same');

[ChrisNZ]

I agree with the previous comments.

As for your code, this works:

%let date_fin = '31OCT2017'd;
%let date_deb = %sysfunc(intnx(month,%sysevalf(&date_fin),-12,sameday));

%macro test;
  %let date = %sysfunc(intnx(month, &date_deb, -12, sameday));
  %put &=date;
%mend test; %test

[Tom]

Note that %SYSEVALF() is not needed where you have it.

You are using the %SYSFUNC() macro function to call the SAS function INTNX(). And unlike the macro processor SAS knows how to deal with date literals (and floating point arithmetic).

The %SYSEVALF() function would only be needed if you wanted to use pure macro code like.

%let tomorrow=%sysevalf('28NOV2017'd +1);

---

@ChrisNZ wrote:

%let date_fin = '31OCT2017'd;
%let date_deb = %sysfunc(intnx(month,%sysevalf(&date_fin),-12,sameday));

---

[Tom · Accepted solution]

Let's take a detailed look at what the code you posted is doing.

First you posted code without a macro definition.  You posted it in this form. 

DATA TEST;
%let date_fin = '31OCT2017'd;
%let date_deb = intnx("month",&date_fin,-12,"sameday");
date = intnx(month, &date_deb., -12, sameday); 
format date date9.;
RUN;

But since the macro processor does its work before the data step code is even compiled what you really asked SAS to do was this.

%let date_fin = '31OCT2017'd;
%let date_deb = intnx("month",&date_fin,-12,"sameday");
DATA TEST;
date = intnx(month, &date_deb., -12, sameday); 
format date date9.;
RUN;

So if we replace the macro variables with their values you are running this code. 

DATA TEST;
date = intnx(month,  intnx("month", '31OCT2017'd-12,"sameday"), -12, sameday); 
format date date9.;
RUN;

 Now that code is going to have problems since the first and last arguments to the outer INTNX() call are the variables MONTH and SAMEDAY which you have not defined anywhere.  We can fix that by converting them into string literals.

DATA TEST;
date = intnx('month',  intnx("month", '31OCT2017'd-12,"sameday"), -12, 'sameday'); 
format date date9.;
RUN;

Now at least it will run and create a dataset with one observation and one variable.

Then you show some more code without a datastep. 

%let date_fin = '31OCT2017'd;
%let date_deb = intnx("month",&date_fin,-12,"sameday");

%macro test;
%let date = %sysfunc(intnx(month, &date_deb, -12, sameday));
%mend test;

The fact that the third %LET statement is inside a macro definition is irrelevant, so we can just remove that complication.

If we replace the macro variable references with their values you end up with.

%let date = %sysfunc(intnx(month, intnx("month",'31OCT2017'd,-12,"sameday"), -12, sameday));

This will not work because you have given the INTNX() function that the macro function %SYSFUNC() is calling an invalid value for the second argument.  It is expecting a date value (since you specified the MONTH interval), but you have given it a text string.  Now it is a text string that worked well when you were using it to generate code as part of a DATA step.  But for the %SYSFUNC(INTNX()) call to work it needs to have an actual date value there and not code.

Most likely what you want to do is to change your second %LET statement to actually calculate a date value instead of just having SAS code that could eventually calculate a date. 

%let date_deb = %sysfunc(intnx(month,&date_fin,-12,sameday));

So for your current value of &DATE_FIN the result will be that DATE_DEB is assigned the value 20758 which is the number that SAS uses to represent 2016-10-31.

Now both your DATA step code (with the fixed INTNX() call) and your %LET statement will generate the same date.

You could if you want make it a little easier to read SYMBOLGEN and MPRINT results generated when you use DATE_DEB if you generated it in the form of a date literal like you did when you assigned a value to DATE_FIN.

%let date_deb = "%sysfunc(intnx(month,&date_fin,-12,sameday),date9)"d;

Now DATE_DEB will have the value "31OCT2016"d instead of 20758. 

Note that both values represent the same number in SAS code.

[viviwu]

That's exatcly what I need! Thank you so much Tom!