topic Re: Error using multiple values in a parameters in SAS Enterprise Guide

Error using multiple values in a parameters

juanvg1972 — Tue, 27 Jun 2017 13:06:10 GMT

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

Re: Error using multiple values in a parameters

RW9 — Tue, 27 Jun 2017 14:15:07 GMT

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;

Re: Error using multiple values in a parameters

juanvg1972 — Tue, 27 Jun 2017 18:16:25 GMT

Thanks,

I have resolved using all values inserted in the parameters

The parameter es segmento

segmento1: fisrst value

segmento2: second calue

segmento3: thris value

Re: Error using multiple values in a parameters

Tom — Tue, 27 Jun 2017 18:24:45 GMT

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)

Re: Error using multiple values in a parameters

FredrikE — Wed, 28 Jun 2017 13:23:35 GMT

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;

Re: Error using multiple values in a parameters

Tom — Wed, 28 Jun 2017 14:32:11 GMT

@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#M23740

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.

Re: Error using multiple values in a parameters

FredrikE — Wed, 28 Jun 2017 14:36:59 GMT

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 🙂

Fredrik