DATA Step, Macro, Functions and more

trouble with sysfunc and intnx in proc sql call

Accepted Solution Solved
Reply
New Contributor jab
New Contributor
Posts: 2
Accepted Solution

trouble with sysfunc and intnx in proc sql call

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.


Accepted Solutions
Solution
‎04-24-2013 04:40 PM
Super User
Posts: 5,441

Re: trouble with sysfunc and intnx in proc sql call

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

Data never sleeps

View solution in original post


All Replies
Solution
‎04-24-2013 04:40 PM
Super User
Posts: 5,441

Re: trouble with sysfunc and intnx in proc sql call

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

Data never sleeps
New Contributor jab
New Contributor
Posts: 2

Re: trouble with sysfunc and intnx in proc sql call

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 1605 views
  • 0 likes
  • 2 in conversation