BookmarkSubscribeRSS Feed
gabras
Pyrite | Level 9

Hi,

 

i'm writing stored procedure in wich i have to pass a list of values in order to use it as a parameter and then use this parameter in a WHERE IN clause.

 

I create a prompt fromlist = 'a','b','c','d',e' which is its default value

 

%macro filter(fromlist);

 proc sql;

select * from have

WHERE var IN (&fromlist);

quit;

 %mend;

 %filter( fromlist = &fromlist)

 

It result in a error :

 ERROR: All positional parameters must precede keyword parameters.

 

 

 

I also tried to use this code:

 

I create a prompt fromlist =     var IN ( 'a','b','c','d',e')    which is its default value

 

%macro filter(fromlist);

 proc sql;

select * from have

WHERE &fromlist;

quit;

%mend;

%filter( fromlist = &fromlist)

 

It result in a error :

ERROR 22-322: Syntax error, expecting one of the following: a string between quotes, a numerical constant, a date and time constant, a missing value, (, -, SELECT.

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

The reason is, is that commas delineate parameters in a macro call so your first call looks like:
%filter( fromlist = 'a','b','c','d',e')

Do from list is 'a' then you have four parameters which are not in the macro.  You also have unbalanced quotes - note the e' and a missing semicolon etc..  

Also it is a very bad idea to put quotes (especially) and commas in macro variables.  Simplify your prompt to accept a list of space delimited non-quoted values and use that:

%let fromlist=a b c d e;

%macro filter(fromlist);
proc sql; select * from have where var in (&fromlist.); quit;
%mend; %filter(fromlist=&fromlist.);

Also note how I use consistent casing, indentation and finish macro variables with a . so that the code is nice and easy to read.

Do be careful naming the macro variable the same as the internal macro variable, it works here because of scope, but if the first on outside the macro was created gloabally...

gabras
Pyrite | Level 9

Hi @RW9

unfortunately it doensn't work.

 

280 %filter ( data_da= &DATA_DA ,
281 data_a = &DATA_A,
282 fromlist= &fromlist.);
ERROR: All positional parameters must precede keyword parameters.

 

"a", "b","c","(here there is the reference of the error to the " )d", ...

ERROR 180-322: Statement is not valid or it is used out of proper order.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Perhaps re-read my post, you still have a macro variable with quotes and commas in it.  Plus you still have the errors which I pointed out to you.

Kurt_Bremser
Super User

As soon as you correct the missing single quote, your second code works:

data have;
input var $;
cards;
a
b
c
d
e
f
g
h
i
j
;
run;

%let fromlist = var IN ('a','b','c','d','e');

%macro filter(fromlist);
proc sql;
select * from have
where &fromlist;
quit;
%mend;

%filter(fromlist = &fromlist)

Result:

var
--------
a       
b       
c       
d       
e       
gabras
Pyrite | Level 9

Hi,

 

i'm using promp given by stored process wizard in SAS EG.

The values in the macro variable list are something like:   abc bcd; efg hil; mno pqr;

 

@RW9 i really don't understand how your suggestion can fit in the where in caondition. maybe i'm missing something.

 

@Kurt_Bremser that code doesn't work. i don't kow why but maybe becasue i'm running a stored process.

 

@BrunoMueller help us please

 

Thank you

 

gabras
Pyrite | Level 9

Hi @Kurt_Bremser

 

the errors of the log are the same that I reported in my first post, since i tried the code you suggested.
Actually it's my fault that I did not specify that the missing single quote was a typo.

gabras
Pyrite | Level 9

Hi @DavidRice

 

could you help  me?

 

Thank you

Kurt_Bremser
Super User

"The log" is not just the error message. The log is the whole log text of your macro definition and macro call. By withholding crucial information, you make it hard for us to help you.

gabras
Pyrite | Level 9

 

@Kurt_Bremser you are right.

 

Let's say i have found a 80% of the solution in this post:

http://bi-notes.com/2013/08/sas-stored-process-taming-selection-list-prompts/

 

I still have a problem because the value in the list promt i submit to the stored procedure is like "Some Some", without double quotes.

 

How can i fix this.

 

Thank you

 

 

BrunoMueller
SAS Super FREQ

Please provide screenshots of your parameter/prompt definition within the Stored Process. We need both information, the General with type etc., as well as the Prompt Type and Values definition

 

Are you using SAS Enterprise Guide to develop your Stored Process?

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 11 replies
  • 2719 views
  • 0 likes
  • 4 in conversation