BookmarkSubscribeRSS Feed
juanvg1972
Pyrite | Level 9

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9

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
Super User Tom
Super User

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
Rhodochrosite | Level 12

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
Super User Tom
Super User

@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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 1096 views
  • 2 likes
  • 4 in conversation