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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.