topic Re: Stored Process - macro variable list used in WHERE IN condition in SAS Programming

Stored Process - macro variable list used in WHERE IN condition

gabras — Thu, 09 Aug 2018 10:08:57 GMT

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.

Re: Stored Process - macro variable list used in WHERE IN condition

RW9 — Thu, 09 Aug 2018 10:24:17 GMT

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

Re: Stored Process - macro variable list used in WHERE IN condition

gabras — Thu, 09 Aug 2018 10:39:51 GMT

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.

Re: Stored Process - macro variable list used in WHERE IN condition

RW9 — Thu, 09 Aug 2018 10:51:33 GMT

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.

Re: Stored Process - macro variable list used in WHERE IN condition

Kurt_Bremser — Thu, 09 Aug 2018 10:59:02 GMT

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

Re: Stored Process - macro variable list used in WHERE IN condition

gabras — Thu, 09 Aug 2018 12:32:25 GMT

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

Re: Stored Process - macro variable list used in WHERE IN condition

Kurt_Bremser — Thu, 09 Aug 2018 12:45:53 GMT

@gabras wrote:

Hi,

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

And? Where is the log?

See http://support.sas.com/documentation/cdl/en/stpug/61271/HTML/default/viewer.htm#exam.htm

"Does not work" on its own is THE most useless statement we come across here.

Re: Stored Process - macro variable list used in WHERE IN condition

gabras — Thu, 09 Aug 2018 12:58:27 GMT

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.

Re: Stored Process - macro variable list used in WHERE IN condition

gabras — Thu, 09 Aug 2018 14:03:42 GMT

Hi @DavidRice

could you help me?

Thank you

Re: Stored Process - macro variable list used in WHERE IN condition

Kurt_Bremser — Thu, 09 Aug 2018 14:09:36 GMT

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

Re: Stored Process - macro variable list used in WHERE IN condition

gabras — Thu, 09 Aug 2018 14:57:08 GMT

@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

Re: Stored Process - macro variable list used in WHERE IN condition

BrunoMueller — Fri, 10 Aug 2018 14:46:35 GMT

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?