DATA Step, Macro, Functions and more

Macro Error

Reply
N/A
Posts: 0

Macro Error

Hi,

I am getting an error while running this below code:

%macro cnt_inv(tbl=, vr=, st=,ds=);
PROC SQL;
create table &tbl.
select count(*) as &vr. from &ds.
where compno in (&st.)
order by clamno lineno;
quit;
%mend cnt_inv;

%cnt_inv(tbl=pr_inv_nj_a, vr=prior_wk_inv, st=&complist, ds=test1);

Error message:
ERROR 180-322: Statement is not valid or it is used out of proper order.

ERROR: All positional parameters must precede keyword parameters

Thanks in Advance
Trusted Advisor
Posts: 2,116

Re: Macro Error

Posted in reply to deleted_user
The second error is probably from &complist, which you don't show. If it has a comma in it, then the %cnt_inv will interpret the parts after the comma as positional parameters. The macro needs a lot more work to handle things like &complist; there are examples in the macro manual.

To get at the first error, you need to add some of the macro debugging options (macrogen in particular would help).

Your order by clause should be a GROUP BY clause to go with the aggregate function COUNT.

Doc Muhlbaier
Duke
Regular Contributor
Posts: 171

Re: Macro Error

Posted in reply to deleted_user
The first error probably occurs because you are missing the word "as" in the create table statement.
N/A
Posts: 0

Re: Macro Error

@ Polingjw : I iincluded the keyword but still am getting an error.
ERROR 180-322: Statement is not valid or it is used out of proper order.

@Duke : I have included the macro debug options as below:

Options symbolgen mlogic mprint;
%put &njcomplist;
%macro cnt_inv (tbl=, vr=, st=, table=);
PROC SQL;
create table '&tbl.' as
select count(*) as "&vr." from '&table.'
where compno in ('&st.')
quit;
%mend cnt_inv;
%cnt_inv(tbl=pr_inv_nj_a, vr=prior_wk_inv, st=&NJcomplist, table=CLA378.outf_old);

Please let me know how to resolve this error?

Thanks
Raveena
Super Contributor
Super Contributor
Posts: 365

Re: Macro Error

Posted in reply to deleted_user
Hello Raveenat,

I see single quotes around all macro variables in SQL Your previous post do not contain them. Variant with quotes is not correct. Please display &njcomplist. it is a good idea to run this code outside of the macro by assigning values (using %let) to all macro variables used.

Sincerely,
SPR
Super User
Posts: 11,343

Re: Macro Error

Posted in reply to deleted_user
If &NJcomplist resolves to having any special characters, you may need to call it as st= %str(&NJcomplist)

Habitual use of a trailing "." with macro variables when you are not concatenating can get you into trouble as well.
Ask a Question
Discussion stats
  • 5 replies
  • 1899 views
  • 0 likes
  • 5 in conversation