Macro Error

[deleted_user]

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

[Doc_Duke]

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

[polingjw]

The first error probably occurs because you are missing the word "as" in the create table statement.

[deleted_user]

@ 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

[SPR]

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

[ballardw]

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.