DATA Step, Macro, Functions and more

%str for multiple values Error

Reply
Frequent Contributor
Posts: 124

%str for multiple values Error

 

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

PROC Star
Posts: 1,547

Re: %str for multiple values Error

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

 

 

Frequent Contributor
Posts: 124

Re: %str for multiple values Error

Posted in reply to novinosrin
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?
PROC Star
Posts: 1,547

Re: %str for multiple values Error

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.) )
Frequent Contributor
Posts: 124

Re: %str for multiple values Error

Posted in reply to novinosrin
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.
PROC Star
Posts: 1,547

Re: %str for multiple values Error

[ Edited ]

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

Frequent Contributor
Posts: 124

Re: %str for multiple values Error

Posted in reply to novinosrin
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));
PROC Star
Posts: 1,547

Re: %str for multiple values Error

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

Super User
Posts: 6,622

Re: %str for multiple values Error

[ Edited ]

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;

Frequent Contributor
Posts: 124

Re: %str for multiple values Error

Posted in reply to Astounding
Hi, Thanks for the reply.
I am getting the below error with the execution -
ERROR: All positional parameters must precede keyword parameters.
Super User
Posts: 6,622

Re: %str for multiple values Error

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

Frequent Contributor
Posts: 124

Re: %str for multiple values Error

Posted in reply to Astounding
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.);


Super User
Posts: 13,283

Re: %str for multiple values Error


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

Super User
Posts: 6,622

Re: %str for multiple values Error

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.

Ask a Question
Discussion stats
  • 13 replies
  • 127 views
  • 0 likes
  • 4 in conversation