ERROR: All positional parameters must precede keyword parameters.

[DRVAS]

Hello friends
Please help with the solution of the problem associated with the output of the parameter in the code.
The code transfers several CELL _PACKAGE_SKs to an Oracle table. But an error occurs during execution
ERROR: All positional parameters must precede keyword parameters

We found that the CELL _PACKAGE_SK parameter is passed to Oracle as a list, but how to pass as a single line.

I would be very grateful for any help in the decision.

Thanks.

%macro Load_Params(CELL_PACKAGE_SK=);
%macro dummy; %mend dummy;

%DO i=1 %TO %eval(%sysfunc(countc(&CELL_PACKAGE_SK.,",")) + 1);
            %let STR=%scan(&CELL_PACKAGE_SK.,&i,",");

            proc sql noprint;
            connect to oracle (AuthDomain="Oracle_CI_COMMON_Auth" PATH="@hostname.ru:1111/CRM");
                        execute 
                        (
begin exchange.pck_params.pr_params_sto_load(&STR.); end;
                        )
                        by oracle;
                        disconnect from oracle;
            quit;

%END;

%mend;
%Load_Params(CELL_PACKAGE_SK=66387,66388);

[Kurt_Bremser]

You supply two parameters to the macro (delimited by the comma), but you only defined one.

[DRVAS]

Tell me, please, where do you need to fix it? i don't get it

[gamotte · Accepted solution]

The comma is interpreted as separating two arguments "a=1" and "2" to the macro call.

You can  use a quote function to avoid this or chose another separator (e.g. space).

%macro mac(a=);
%put &=a.;
%mend;

%mac(a=1,2) /* two parameters => boom! */
%mac(a=%quote(1,2)) /* one parameter */
%mac(a=1 2) /* two parameter */

[DRVAS]

I considered the option of replacing the separator in place of the comma put the underscore. But this method does not work in Oracle. Oracle most often uses comma delimiter

Thank you for your interest

[PaigeMiller]

---

@DRVAS wrote:

I considered the option of replacing the separator in place of the comma put the underscore. But this method does not work in Oracle. Oracle most often uses comma delimiter.

---

I'm not following this. Your code sent to ORACLE by this macro does not have a comma:

begin exchange.pck_params.pr_params_sto_load(&STR.); end;

there is no comma here 

[gamotte]

If you want to pass a comma separated list to your macro and use it as is you can use the %quote function

in the macro call to avoid interpreting the comma as an argument separator.

See the second example in my previous answer.

[PaigeMiller]

---

@gamotte wrote:

If you want to pass a comma separated list to your macro and use it as is you can use the %quote function

in the macro call to avoid interpreting the comma as an argument separator.

See the second example in my previous answer.

---

I think this would work if a comma separated list is really needed.

%Load_Params(CELL_PACKAGE_SK=66387%str(,)66388);

But as I said above, the code sent to ORACLE doesn't contain a comma.

[DRVAS]

I apologize, your decision was also correct. But not obvious to me. My fault

[gamotte]

No need to apologize. Don't hesitate to ask for clarifications or examples if you don't fully understand an answer.

[DRVAS]

Thank you for understanding.
Faced the following problem. If you substitute the% NRBQUOTE function in LOAD_PARAM, then sas passes CELL_PACKAGE_SK one by one, and this is not what I need. I would like sas to pass CELL_PACKAGE_SK parameters in one line.

An example from the log how the process works now:

MLOGIC(LOAD_PARAMS):  Beginning execution.
SYMBOLGEN:  Macro variable CELL_PACKAGE_SK resolves to 102118,102119
MLOGIC(LOAD_PARAMS):  Parameter CELL_PACKAGE_SK has value 102118102119
SYMBOLGEN:  Macro variable CELL_PACKAGE_SK resolves to 102118,102119
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(LOAD_PARAMS):  %DO loop beginning; index variable I; start value is 1; stop value is 2; by value is 1.  
MLOGIC(LOAD_PARAMS):  %LET (variable name is STR)
SYMBOLGEN:  Macro variable CELL_PACKAGE_SK resolves to 102118,102119
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN:  Macro variable I resolves to 1
MPRINT(LOAD_PARAMS):   proc sql noprint;
MPRINT(LOAD_PARAMS):   connect to oracle (AuthDomain="Oracle_Auth" PATH="@sas-te-list.or:1111/CRM");
NOTE:  Credential obtained from SAS metadata server.
SYMBOLGEN:  Macro variable STR resolves to 102118
MPRINT(LOAD_PARAMS):   execute ( begin exchange.pck_params.pr_params_sto_load(102118);
MPRINT(LOAD_PARAMS):   end;
MPRINT(LOAD_PARAMS):   ) by oracle;
MPRINT(LOAD_PARAMS):   disconnect from oracle;
MPRINT(LOAD_PARAMS):   quit;

      

MLOGIC(LOAD_PARAMS):  %DO loop index variable I is now 2; loop will iterate again.
MLOGIC(LOAD_PARAMS):  %LET (variable name is STR)
SYMBOLGEN:  Macro variable CELL_PACKAGE_SK resolves to 102118,102119
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN:  Macro variable I resolves to 2
MPRINT(LOAD_PARAMS):   proc sql noprint;
MPRINT(LOAD_PARAMS):   connect to oracle (AuthDomain="Oracle_Auth" PATH="@sas-te-list.or:1111/CRM");
NOTE:  Credential obtained from SAS metadata server.
SYMBOLGEN:  Macro variable STR resolves to 102119
MPRINT(LOAD_PARAMS):   execute ( begin exchange.pck_params.pr_params_sto_load(102119);
MPRINT(LOAD_PARAMS):   end;
MPRINT(LOAD_PARAMS):   ) by oracle;
MPRINT(LOAD_PARAMS):   disconnect from oracle;
MPRINT(LOAD_PARAMS):   quit;

The question is the same, how to pass CELL_PACKAGE_SK parameters to Oracle in one line from sas.

[PaigeMiller]

---

@DRVAS wrote:

Thank you for understanding.
Faced the following problem. If you substitute the% NRBQUOTE function in LOAD_PARAM, then sas passes CELL_PACKAGE_SK one by one, and this is not what I need. I would like sas to pass CELL_PACKAGE_SK parameters in one line.

---

You have a loop which causes the parameters to be passed one-by-one to Oracle. If you take out that loop, and use %NRBQUOTE(), all should be passed to Oracle as one large string with commas.

[Tom]

I don't understand what your question is here.  

The commas in the value of the macro variable cause trouble when passing the value into the macro in the macro call (since comma is a delimiter to the macro calling syntax).  Once you have the commas in the value of the macro variable you should be able to use that value to generate any syntax you need that requires commas.

So your macro variable has two values separated by a comma:

SYMBOLGEN:  Macro variable CELL_PACKAGE_SK resolves to 102118,102119

If you want to generate this Oracle procedure call:

execute (begin exchange.pck_params.pr_params_sto_load(102118,102119); end;) by oracle;

Then just use a reference to the macro variable instead of the hard coded list of values.

execute (begin exchange.pck_params.pr_params_sto_load(&CELL_PACKAGE_SK); end;) by oracle;

If your Oracle package needs some other syntax to handle multiple values in a single call then explain what that syntax is and adjust your SAS code the generate the syntax that Oracle needs.

[PaigeMiller]

%Load_Params(CELL_PACKAGE_SK=66387,66388);

A comma here indicates that this is the end of the parameter CELL_PACKAGE_SK and value 66387 is assigned to macro variable &CELL_PACKAGE_SK. Then 66388 does not make any sense in this context, because your macro has not defined another macro variable that 66388 could be assigned to.

If you get rid of the comma and use a space, then you don't get this error, because the comma has a special meaning. Of course, then you have to modify the rest of the macro which is looking for a comma.

[DRVAS]

It really helps, but the problem arises already at the Oracle level, the database understands nothing but a comma separator. Therefore, I turned here for help

thanks for the advice