How to correct: ERROR: All positional parameters must precede keyword parameters.

[RobertNYC]

Hi all,

I’m having trouble getting the macro below to work.   The run is fine for these first three passes:

%prod(NBIZ=RLS, biz=1);

%prod(NBIZ=PFS, biz=2);

%prod(NBIZ=RLE, biz=3);

But I’m running into an error when I try to run these two passes:

%prod(NBIZ=CMUS, biz= 4,14);

%prod(NBIZ=CMCA, biz= 5,15);

68         %prod(NBIZ=CMUS, biz= 4,14);

ERROR: All positional parameters must precede keyword parameters.

69         %prod(NBIZ=CMCA, biz= 5,15);

ERROR: All positional parameters must precede keyword parameters.

I'm having trouble figuring out how to correct this.  Any assistance will be greatly appreciated.  Thanks! 

%macro prod (NBIZ=,biz=); 
proc sql;
drop table ora.&NBIZ._PRODL1;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table  ora.&NBIZ._PRODL1  as
select *
from connection to oracle (
SELECT DISTINCT business_unit,
    CASE WHEN group_div = 999999 THEN group_div ELSE group_div || '0' || business_unit END AS group_div,
    group_div_desc
  FROM mbs.sas_sku_xref_vw
  WHERE business_unit in (&biz.));
disconnect from oracle;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table   ora.&NBIZ._PRODL2  as
select *
from connection to oracle (
 select DISTINCT business_unit,
    case WHEN division = 999999 THEN division ELSE division || '0' || business_unit END AS division, division_desc
  from mbs.sas_sku_xref_vw
  WHERE business_unit in (&biz.));
disconnect from oracle;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table   ora.&NBIZ._PRODL3  as
select *
from connection to oracle (
  SELECT DISTINCT business_unit,
    CASE WHEN department = 999999 then  department else  department || '0' || business_unit end as department, department_desc
  FROM mbs.sas_sku_xref_vw
  WHERE business_unit in (&biz.));
disconnect from oracle;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table   ora.&NBIZ._PRODL4  as
select *
from connection to oracle (
  SELECT DISTINCT business_unit,
    CASE WHEN sub_department = 999999 THEN sub_department ELSE sub_department || '0' || business_unit END AS sub_department, sub_department_desc
  FROM mbs.sas_sku_xref_vw
  WHERE business_unit in (&biz.));
disconnect from oracle;quit;
%mend;
%prod(NBIZ=RLS, biz=1);
%prod(NBIZ=PFS, biz=2);
%prod(NBIZ=RLE, biz=3);
%prod(NBIZ=CMUS, biz= 4,14);
%prod(NBIZ=CMCA, biz= 5,15);

[Astounding · Accepted solution]

Editor's note:  This answer is correct but if you need the commas to be part of the value you would say:

  %prod(nbiz=cmus, biz=%str(4,14))

The following is also a link to the macro documentation that discusses macro parameters that you might find helpful:

https://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#p1nypovnwon4uyn1...

As you have seen, commas separate parameters when calling a macro.  Thus the commas that are NOT separating parameters cause confusion for macro language.

The simplest solution is to get rid of the extra commas in this case:

%prod(NBIZ=CMUS, biz= 4 14)

When substituted into the WHERE clause, the commas are optional.  So removing the commas won't hurt, and makes the macro calls simpler.

[RobertNYC]

Hi, 

Thanks for your help.  

I still could not seem to get the query to work after I remove the comma.   See the error below.

ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis. SQL statement: SELECT DISTINCT business_unit, CASE WHEN
group_div = 999999 THEN group_div ELSE group_div || '0' || business_unit END AS group_div, group_div_desc FROM
mbs.sas_sku_xref_vw WHERE business_unit in (5 15).
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Statement not executed due to NOEXEC option.
NOTE: Statement not executed due to NOEXEC option.

%macro prod (NBIZ=,biz=); 
proc sql;
drop table ora.&NBIZ._PRODL1; 
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table ora.&NBIZ._PRODL1 as
select *
from connection to oracle (
SELECT DISTINCT business_unit,
CASE WHEN group_div = 999999 THEN group_div ELSE group_div || '0' || business_unit END AS group_div,
group_div_desc
FROM mbs.sas_sku_xref_vw
WHERE business_unit in (&biz.));
disconnect from oracle;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table ora.&NBIZ._PRODL2 as
select *
from connection to oracle (
select DISTINCT business_unit,
case WHEN division = 999999 THEN division ELSE division || '0' || business_unit END AS division, division_desc
from mbs.sas_sku_xref_vw
WHERE business_unit in (&biz.));
disconnect from oracle;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table ora.&NBIZ._PRODL3 as
select *
from connection to oracle (
SELECT DISTINCT business_unit,
CASE WHEN department = 999999 then department else department || '0' || business_unit end as department, department_desc
FROM mbs.sas_sku_xref_vw
WHERE business_unit in (&biz.));
disconnect from oracle;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table ora.&NBIZ._PRODL4 as
select *
from connection to oracle (
SELECT DISTINCT business_unit,
CASE WHEN sub_department = 999999 THEN sub_department ELSE sub_department || '0' || business_unit END AS sub_department, sub_department_desc
FROM mbs.sas_sku_xref_vw
WHERE business_unit in (&biz.));
disconnect from oracle;quit;
%mend;
%prod(NBIZ=RLS, biz=1);
%prod(NBIZ=PFS, biz=2);
%prod(NBIZ=RLE, biz=3);
%prod(NBIZ=CMUS, biz= 4 14);
%prod(NBIZ=CMCA, biz= 5 15);

[Astounding]

Yes, it's possible that you will have many errors to debug.  This first step was only to get the macro to run at all.

Here's a way you can isolate the errors one at a time, so you can fix them.

Remove the bottom SELECT statements.  Get the first CONNECT/SELECT/DISCONNECT group to work.  You don't even need macro language to do this.  Just take the code out of the macro, and hard-code the NBIZ and BUSINESS_UNIT value(s).  Once that section of code is working, add in the next CONNECT/SELECT/DISCONNECT group.  By removing the macro language in order to debug, the error messages will become much clearer.  SAS will do a better job locating the source of the error.