trouble with sysfunc and intnx in proc sql call

[jab]

I have some basic SAS code that I'm not trying to turn into a macro. I've taken the quotes off of the the first argument to avoid that error, but now I have a new one.

What is wrong with this?

This works just fine:

proc sql;

  create table tester as

  select distinct a.*, b.newvalue

  from datasetabc as a, datasetxyz as b

  where a.id = b.id

  and month(a.date) = month(intnx('month',b.date,1)) and year(a.date) = year(intnx('month',b.date,1))

  order by id, a.date;

quit;

However, when I put it inside a macro, this gives an error:

%macro test_intnx()

proc sql;

  create table tester as

  select distinct a.*, b.newvalue

  from datasetabc as a, datasetxyz as b

  where a.id = b.id

  and month(a.date) = month(%sysfunc(intnx(month,b.date,1))) and year(a.date) = year(%sysfunc(intnx(month,b.date,1)))

  order by id, a.date;

quit;

%mend();

%test_intnx();

Error:

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 or %QSYSFUNC function reference is terminated.

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 or %QSYSFUNC function reference is terminated.

[LinusH · Accepted solution]

Why are you using %sysfunc in the macro version?

%sysfunc calls works fine when you call it using constants - you cannot pass arguments from the value of a SQL expression.

If your intention is just to put your SQL inside a macro, just skip the %sysfunc.

Why a macro? You are not passing any macro parameters...

[jab]

The macro parameter I'm changing was in the dataset b's name, I omitted it for simplicity to clearly show the problem I was having.

I thought I needed the %sysfunc there to correctly execute the intnx function - I thought it fixed a previous error (the SAS macro language still confuses me) but as you indicated, if I take it out, it works just fine. Thanks.