I'm running this sql macro in SAS.
%macro calc(table=,cut=,whereclause=);
proc sql;
&table
select
&cut as type format = $40. length = 40
,dt
,count(prod_nbr) as stat
,sum(new) as new
,sum(old) as old
,sum(retired) as retired
,sum(replaced) as replaced
,sum(final) as final
,sum(redo) as redo
from work.product
where retail_flg = 1
&whereclause
group by 1,2;
quit;
%mend calc;
I call the macro about sixty times in the program and it works about 80% of the time when I call it. But every once and a while it will generate this error:
ERROR: All positional parameters must precede keyword parameters
If I run the code in the same order, the error always shows at the same line. But if I start running the calls in different orders the error will eventually occur on a seemingly random line of code that calls the macro. Here is an example of one of the calls that it gets caught up on (after calc table has already been created):
%calc(table = insert into calc, cut = 'Product', whereclause = and brand = 'JNJ' and Prod_type = 'N' and index(prod_nm, 'NEW') > 0);
I'm especially confused by the error since I don't have any positional parameters in the macro. I've researched and trouble shot for syntax errors and other common issues without being able to resolve the error.
Most likely you have unbalanced quotes somewhere. Perhaps some value you have the the WHERECLAUSE has unbalanced quotes. I would look at the value BEFORE the one that generates the error message. It might also be the result of code being truncated. For example if you are writing the generated code to a file some long WHERECLAUSE value might get truncated and result in unbalanced quotes.
Adding some extra line breaks in your macro calls can make it easier to check for errors since shorter lines are easier for humans to scan.
%calc
(table = insert into calc
,cut = 'Product'
,whereclause =
and brand = 'JNJ' and Prod_type = 'N'
and index(prod_nm, 'NEW') > 0
);