topic Re: %str for multiple values Error in SAS Programming

%str for multiple values Error

SASAna — Wed, 04 Apr 2018 19:03:32 GMT

Hi SAS users,

I am getting "ERROR: All positional parameters must precede keyword parameters." while running the below code .

how to use the multiple values of the macro's when passing in the Macro call?

%let NRML_CODE4 = '1','2','3';
%let UPCDED_CODE4 = 'A', 'B', 'C';
%let GRP_CD4 = 'X';

%macro data_pull(NRML_CODE =,UPCDED_CODE =,GRP_CD =);

proc sql;
create table grp_clms as
select * from sashelp.cars
where (model in %str(&NRML_CODE., &UPCDED_CODE.))
;
run;

%mend;

%data_pull(nrml_code = &NRML_CODE4. ,upcded_code = &UPCDED_CODE4., grp_cd= &GRP_CD4. );

Re: %str for multiple values Error

novinosrin — Wed, 04 Apr 2018 19:10:59 GMT

My eyesight isn't the best to fix syntax but try the correction attempt of mine:

%let NRML_CODE4 = %str('1','2','3');
%let UPCDED_CODE4 = %str('A', 'B', 'C');
%let GRP_CD4 = %str('X');

%macro data_pull(NRML_CODE =,UPCDED_CODE =,GRP_CD =);

proc sql;
create table grp_clms  as
select * from sashelp.cars
  where model in (&NRML_CODE., &UPCDED_CODE.)
;
quit;

%mend;

%data_pull(nrml_code =  &NRML_CODE4. ,upcded_code = &UPCDED_CODE4., grp_cd= &GRP_CD4. )

Re: %str for multiple values Error

SASAna — Wed, 04 Apr 2018 19:18:21 GMT

Thanks for the Reply. It works in the %LET statement. But these values are coming from Database. So i was looking for %str in the Macro code or Call. Any suggestions on that?

Re: %str for multiple values Error

novinosrin — Wed, 04 Apr 2018 19:25:56 GMT

something like this?

%macro data_pull(NRML_CODE =,UPCDED_CODE =,GRP_CD =);

proc sql;
create table grp_clms  as
select * from sashelp.cars
  where model in (&NRML_CODE., &UPCDED_CODE.)
;
quit;

%mend;

%data_pull(%superq(nrml_code =  &NRML_CODE4.) ,%superq(upcded_code = &UPCDED_CODE4.), %superq(grp_cd= &GRP_CD4.) )

Re: %str for multiple values Error

Astounding — Wed, 04 Apr 2018 19:34:37 GMT

Do it the easy way. Get rid of all the commas.

%let NRML_CODE4 = '1' '2' '3';
%let UPCDED_CODE4 = 'A' 'B' 'C';
%let GRP_CD4 = 'X';

proc sql;
create table grp_clms as
select * from sashelp.cars
where model in (&NRML_CODE. &UPCDED_CODE.)
;

quit;

Re: %str for multiple values Error

SASAna — Wed, 04 Apr 2018 19:48:16 GMT

Thanks for the reply. But i am getting the below errors.

%data_pull(%superq(nrml_code = &NRML_CODE4.) ,%superq(upcded_code = &UPCDED_CODE4.), %superq(grp_cd= &GRP_CD4.) )
ERROR: Invalid symbolic variable name NRML_CODE '1','2','3'.
ERROR: Invalid symbolic variable name UPCDED_CODE 'A', 'B', 'C'.
ERROR: Invalid symbolic variable name GRP_CD 'X'.
ERROR: More positional parameters found than defined.

Re: %str for multiple values Error

SASAna — Wed, 04 Apr 2018 19:51:28 GMT

Hi, Thanks for the reply.
I am getting the below error with the execution -
ERROR: All positional parameters must precede keyword parameters.

Re: %str for multiple values Error

novinosrin — Wed, 04 Apr 2018 20:05:03 GMT

i am so sorry

%data_pull(nrml_code = %superq( NRML_CODE4) ,upcded_code = %superq(UPCDED_CODE4), grp_cd= %superq(GRP_CD4) )

this should. my bad and sincere apologies for the syntax

Re: %str for multiple values Error

Astounding — Wed, 04 Apr 2018 20:06:44 GMT

You'll have to show what you ran ... similar to your original post. It's easy to debug, but I have to see what you ran (including the statement that calls the macro).

Re: %str for multiple values Error

SASAna — Wed, 04 Apr 2018 21:46:50 GMT

Thanks for the help. I am still getting the same error Message as before .
%let NRML_CODE4 = '1','2','3';
%let UPCDED_CODE4 = 'A', 'B', 'C';
%let GRP_CD4 = 'X';

%macro data_pull(NRML_CODE =,UPCDED_CODE =,GRP_CD =);

proc sql;
create table grp_clms as
select * from sashelp.cars
where model in (&NRML_CODE. , &UPCDED_CODE.)
;
quit;

%mend;

*%data_pull(nrml_code = &NRML_CODE4. , upcded_code = &UPCDED_CODE4., grp_cd= &GRP_CD4.);

%data_pull(nrml_code = %superq(&NRML_CODE4),upcded_code = %superq(&UPCDED_CODE4), grp_cd= %superq(&GRP_CD4));

Re: %str for multiple values Error

SASAna — Wed, 04 Apr 2018 21:48:55 GMT

Hi Astounding,

I ran the exact change you have asked me to test on . and getting "ERROR: All positional parameters must precede keyword parameters.
"

%let NRML_CODE4 = '1','2','3';
%let UPCDED_CODE4 = 'A', 'B', 'C';
%let GRP_CD4 = 'X';

%macro data_pull(NRML_CODE =,UPCDED_CODE =,GRP_CD =);

proc sql;
create table grp_clms as
select * from sashelp.cars
where model in (&NRML_CODE. &UPCDED_CODE.)
;
quit;

%mend;

%data_pull(nrml_code = &NRML_CODE4. , upcded_code = &UPCDED_CODE4., grp_cd= &GRP_CD4.);

Re: %str for multiple values Error

novinosrin — Wed, 04 Apr 2018 22:16:43 GMT

Look at my example above, i don't know why you didn;t remove the & before the macro variable in the %superq function.

Re: %str for multiple values Error

ballardw — Wed, 04 Apr 2018 22:43:44 GMT

@SASAna wrote:
Hi Astounding,

I ran the exact change you have asked me to test on . and getting "ERROR: All positional parameters must precede keyword parameters.
"

%let NRML_CODE4 = '1','2','3';
%let UPCDED_CODE4 = 'A', 'B', 'C';
%let GRP_CD4 = 'X';

%macro data_pull(NRML_CODE =,UPCDED_CODE =,GRP_CD =);

proc sql;
create table grp_clms as
select * from sashelp.cars
where model in (&NRML_CODE. &UPCDED_CODE.)
;
quit;

%mend;

%data_pull(nrml_code = &NRML_CODE4. , upcded_code = &UPCDED_CODE4., grp_cd= &GRP_CD4.);

See the commas highlighted. I believe the suggestion was to remove the commas...

When you submit this statement:

%data_pull(nrml_code = &NRML_CODE4. , upcded_code = &UPCDED_CODE4., grp_cd= &GRP_CD4.);

The macro variables resolve to in actual call:

%data_pull(nrml_code = '1','2','3' , upcded_code = 'A', 'B', 'C', grp_cd= 'X');

Notice that the first comma between '1' and '2' is treated a delimiter between the macro parameters just as in the definition the comma between nrml_code= and upcded_code is a delimiter. So when the value '2' is encountered is treated as a positional parameter since there is nothing like var= between the comma and the value '2'.

So either ensure that the value passed to the macro call does not contain commas OR use a call like

%data_pull(nrml_code = %str(&NRML_CODE4.) , upcded_code = %str(&UPCDED_CODE4.), grp_cd= &GRP_CD4.);

The %str or %nrstr are one way to prevent the compiler from seeing the commas "too soon". Different macro quoting functions may be needed depending on the actual types of characters that get sent. Note that the macro language characters & and % would need %nrstr instead %str. Anything that might contain a single quote, either ' or " as part of the value may also cause serious issues.

Consider what might happen if you have open code like:

where model in ('1' '2' '3' 'this's' 'had imbedded quote')

when one of the data base values had the value of "this's". Other problem values from your database could involve ( ) as unmatched pairs.

You have to determine all of the possible cases of special characters based on you data content. Note that variables that contain units of measure have caused similar headaches for me when the values were like 10' 3". Company names like Johnson & Sons may also cause headaches.

The IN operator has not required commas for several releases so if the extract from the data base can be done without them you'll be ahead of the game.

Re: %str for multiple values Error

Astounding — Thu, 05 Apr 2018 03:28:34 GMT

The exact code that I showed does not contain commas. You still have commas instead of blanks in your %LET statements. Change them to blanks.