Error using multiple values in a parameters

[juanvg1972]

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

[RW9]

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;

[juanvg1972]

Thanks,

I have resolved using all values inserted in the parameters

The parameter es segmento

segmento1: fisrst value

segmento2: second calue

segmento3: thris value

[Tom]

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)

[FredrikE]

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

[Tom]

---

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

[FredrikE]

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 🙂

BR

Fredrik