BookmarkSubscribeRSS Feed
SASAna
Quartz | Level 8

 

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

13 REPLIES 13
novinosrin
Tourmaline | Level 20

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

 

 

SASAna
Quartz | Level 8
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?
novinosrin
Tourmaline | Level 20

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.) )
SASAna
Quartz | Level 8
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.
novinosrin
Tourmaline | Level 20

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

SASAna
Quartz | Level 8
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));
novinosrin
Tourmaline | Level 20

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

Astounding
PROC Star

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;

SASAna
Quartz | Level 8
Hi, Thanks for the reply.
I am getting the below error with the execution -
ERROR: All positional parameters must precede keyword parameters.
Astounding
PROC Star

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

SASAna
Quartz | Level 8
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.);


ballardw
Super User

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

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 988 views
  • 0 likes
  • 4 in conversation