Desktop productivity for business analysts and programmers

Error using multiple values in a parameters

Reply
Frequent Contributor
Posts: 90

Error using multiple values in a parameters

Hi, I am using a parameter in Guide that accept multiple values in a static list. This parameter is text and is called: segmento.

Now, in a query builder I am using this parameter in a where clause, like this:

where cod_segmento in ("&segmento")

I have to use " because if not I have an error

When I execute I select three values of segmento in the prompt, but the SQL only takes the first one.

Is there any solution?, do I have to define the param in other way?

 

Thanks in advance

Esteemed Advisor
Esteemed Advisor
Posts: 7,189

Re: Error using multiple values in a parameters

[ Edited ]

What does &segmento. resolve to?  If you macro variable looks like this:

ABC DEF

Then your code will look like this:

where cod_segmento in ("ABC DEF")

Which isn't what you want, so you would have to manipluate it a bit:

%let segmento=M F;

/* Example out of code */ %put %sysfunc(tranwrd("&segmento.",%str( ),","));
/* Example in where clause */ data want; set sashelp.class; where sex in (%sysfunc(tranwrd("&segmento.",%str( ),","))); run;

 

Frequent Contributor
Posts: 90

Re: Error using multiple values in a parameters

Thanks,

 

I have resolved using all values inserted in the parameters

 

The parameter es segmento

 

segmento1: fisrst value

segmento2: second calue

segmento3: thris value

Super User
Super User
Posts: 6,308

Re: Error using multiple values in a parameters

You will get mulitple macro variables to hold the multiple selections.

You will need to pre-process them to make the useful in the WHERE clause.

So something like this:

data _null_;
  length str $32767 ;
  do i=1 to &segmento_count ;
    str = catx(' ',quote(cats('&segmento',i)));
  end;
  call symputx('segmento_all',str);
run;

Will create a new macro variable named SEGMENTO_ALL with values like "&segmento1" "&segment2" .... based on how many values where selected.

Then your WHERE clause can use that new macro variable.

where cod_segmento in (&segmento_all)

 

Frequent Contributor
Posts: 120

Re: Error using multiple values in a parameters

Tom's solution is good, but you can also use EG's own macros:

data ...;

 set ....;

 where %_eg_WhereParam( cod_segmento, segmento, IN, TYPE=S, IS_EXPLICIT=0 )

run;

 

Read more in this post:

https://communities.sas.com/t5/SAS-Enterprise-Guide/Prompt-Manager-question-with-SAS-EG/m-p/361199#M...

Super User
Super User
Posts: 6,308

Re: Error using multiple values in a parameters


FredrikE wrote:

Tom's solution is good, but you can also use EG's own macros:

data ...;

 set ....;

 where %_eg_WhereParam( cod_segmento, segmento, IN, TYPE=S, IS_EXPLICIT=0 )

run;

 

Read more in this post:

https://communities.sas.com/t5/SAS-Enterprise-Guide/Prompt-Manager-question-with-SAS-EG/m-p/361199#M...


Where can we find the source for this macro?  It is not in the autocall macros that come with SAS. It must be with Enterprise Guide somewhere.

Frequent Contributor
Posts: 120

Re: Error using multiple values in a parameters

I actually don't know, as you says it's in EG somewhere.

If you use prompts in queries, you can se the macro-statements in the code Smiley Happy

BR

Fredrik

Ask a Question
Discussion stats
  • 6 replies
  • 110 views
  • 1 like
  • 4 in conversation