DATA Step, Macro, Functions and more

Macro Error

Posts: 0

Macro Error


I am getting an error while running this below code:

%macro cnt_inv(tbl=, vr=, st=,ds=);
create table &tbl.
select count(*) as &vr. from &ds.
where compno in (&st.)
order by clamno lineno;
%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,113

Re: Macro Error

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
Regular Contributor
Posts: 171

Re: Macro Error

The first error probably occurs because you are missing the word "as" in the create table statement.
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=);
create table '&tbl.' as
select count(*) as "&vr." from '&table.'
where compno in ('&st.')
%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?

Super Contributor
Super Contributor
Posts: 365

Re: Macro Error

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.

Super User
Posts: 10,490

Re: Macro Error

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
  • 5 in conversation