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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

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

 

 

 

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);

 


Accepted Solutions
Solution
‎08-30-2017 01:40 PM
Super User
Posts: 5,503

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

[ Edited ]
Posted in reply to RobertNYC

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.

View solution in original post


All Replies
Solution
‎08-30-2017 01:40 PM
Super User
Posts: 5,503

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

[ Edited ]
Posted in reply to RobertNYC

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.

Frequent Contributor
Posts: 101

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

Posted in reply to Astounding

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);
Super User
Posts: 5,503

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

Posted in reply to RobertNYC

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 4409 views
  • 0 likes
  • 2 in conversation