topic Re: ERROR: All positional parameters must precede keyword parameters. in SAS Programming

ERROR: All positional parameters must precede keyword parameters.

DRVAS — Fri, 06 Dec 2019 12:25:07 GMT

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

Re: ERROR: All positional parameters must precede keyword parameters.

Kurt_Bremser — Fri, 06 Dec 2019 12:44:10 GMT

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

Re: ERROR: All positional parameters must precede keyword parameters.

DRVAS — Fri, 06 Dec 2019 12:57:23 GMT

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

Re: ERROR: All positional parameters must precede keyword parameters.

gamotte — Fri, 06 Dec 2019 13:09:34 GMT

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 */

Re: ERROR: All positional parameters must precede keyword parameters.

PaigeMiller — Fri, 06 Dec 2019 13:09:09 GMT

%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.

Re: ERROR: All positional parameters must precede keyword parameters.

DRVAS — Fri, 06 Dec 2019 13:10:52 GMT

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

Re: ERROR: All positional parameters must precede keyword parameters.

DRVAS — Fri, 06 Dec 2019 13:17:27 GMT

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

Re: ERROR: All positional parameters must precede keyword parameters.

PaigeMiller — Fri, 06 Dec 2019 13:21:44 GMT

@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

Re: ERROR: All positional parameters must precede keyword parameters.

gamotte — Fri, 06 Dec 2019 13:18:34 GMT

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.

Re: ERROR: All positional parameters must precede keyword parameters.

PaigeMiller — Fri, 06 Dec 2019 13:28:08 GMT

@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.

Re: ERROR: All positional parameters must precede keyword parameters.

unison — Fri, 06 Dec 2019 14:52:27 GMT

Take a look at this approach.

%macro load_params(cell_package_sk=);

%put param passed in = &cell_package_sk.;

*removing parentheses..;
%let rm_parens = %sysfunc(compress(&cell_package_sk.,"()"));
%put string after removing parentheses = &rm_parens.;

*number of commas;
%let num_commas = %qsysfunc(countc(%superq(rm_parens),%str(,)));
%put number of commas (tokens = num_commas+1) = &num_commas.;

*loop over tokens;
%do i=1 %to &num_commas.+1;
	%let token = %qscan(%superq(rm_parens),&i.,%str(,));
	%put current token (&i.) = &token.;

	*----your code here---;
%end;
%mend;
%let input = (66387,66388);
%load_params(cell_package_sk=&input);

Realistically: If you're eventually parsing out the comma-delimited list and throwing that into your oracle statement, you shouldn't go through the trouble of using a comma-delimited list to begin with.

-unison

Re: ERROR: All positional parameters must precede keyword parameters.

Tom — Fri, 06 Dec 2019 15:24:28 GMT

@DRVAS wrote:

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

Personally I prefer to use space delimited list. Or if space is in the actual data then some other character, like | or ^ or ~.

Then if you need to convert them into commas to pass to something , usually SQL, that needs commas, you can always convert them in the macro.

%macro mymac(mylist);
%local mylistc;
%let mylistc=%sysfunc(translate(&mylist,%str(,),%str( )));
....
from connection to oracle 
( .... x in (&mylistc) ....);
...
%mend mymac;

%mymac(mylist=1 2 3 4 5);

Re: ERROR: All positional parameters must precede keyword parameters.

ballardw — Fri, 06 Dec 2019 15:48:31 GMT

Generate the code that makes this work without any macro variables or macro calls.

Once you have that working try the suggestions provided with your macro call.

Set OPTIONS MPRINT prior to running the macro.

Compare the results of the MPRINT output with the code that you wrote that worked.

Re: ERROR: All positional parameters must precede keyword parameters.

DRVAS — Mon, 16 Dec 2019 15:37:27 GMT

Good day.
Thank you all for your active help.
But the solution was unexpected.
Because When calling% Load_params, a delimited error occurred. Then we used the% NRBQUOTE function
Example:
% Load_Params (CELL_PACKAGE_SK =% NRBQUOTE (& CELL_PACKAGE_SK.));

Re: ERROR: All positional parameters must precede keyword parameters.

DRVAS — Mon, 16 Dec 2019 15:43:35 GMT

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

Re: ERROR: All positional parameters must precede keyword parameters.

gamotte — Tue, 17 Dec 2019 08:45:04 GMT

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

Re: ERROR: All positional parameters must precede keyword parameters.

DRVAS — Wed, 18 Dec 2019 13:31:41 GMT

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.

Re: ERROR: All positional parameters must precede keyword parameters.

PaigeMiller — Wed, 18 Dec 2019 13:50:18 GMT

@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.

Re: ERROR: All positional parameters must precede keyword parameters.

Tom — Wed, 18 Dec 2019 14:36:52 GMT

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.