topic Re: Macro Error in SAS Programming

Macro Error

deleted_user — Tue, 08 Mar 2011 15:04:17 GMT

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

Re: Macro Error

Doc_Duke — Tue, 08 Mar 2011 15:16:51 GMT

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

Re: Macro Error

polingjw — Tue, 08 Mar 2011 15:33:02 GMT

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

Re: Macro Error

deleted_user — Tue, 08 Mar 2011 17:14:45 GMT

@ 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

Re: Macro Error

SPR — Tue, 08 Mar 2011 21:15:29 GMT

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

Re: Macro Error

ballardw — Tue, 08 Mar 2011 22:57:25 GMT

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.