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.
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...
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...
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.