topic Re: trouble with sysfunc and intnx in proc sql call in SAS Programming

trouble with sysfunc and intnx in proc sql call

jab — Wed, 24 Apr 2013 20:36:16 GMT

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.

Re: trouble with sysfunc and intnx in proc sql call

LinusH — Wed, 24 Apr 2013 20:40:53 GMT

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

Re: trouble with sysfunc and intnx in proc sql call

jab — Wed, 24 Apr 2013 20:49:08 GMT

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.