BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Martin_Bryant
Quartz | Level 8

I'm trying to use _eg_WhereParam but with limited success. Is there any documentation for this?

 

I've got the basics but I need something that explains every option that can be used with it please.

 

In my current code I have a multi-select prompt which is to be used in a proc sql; where clause such as

 

%_eg_WhereParam( OptionType, PromptOption, IN, TYPE=S)

 

however if the user doesn't select any items (the default option) then I want to effectively ignore this where clause.

 

This would be the same as selecting all items from the list, but asking the user to select all items would be poor UI design and result in poor code. The best option is to be able to say if the selection is blank, ignore this where clause. I've found an example with things like...

 

IS_EXPLICIT=0, MATCHALL=_ALL_VALUES_, MATCHALL_CLAUSE=1

 

but with no explanation as to what they do or if other options can be used.

 

Another option is to check the prompt value myself, but if nothing has been selected I get 

WARNING: Apparent symbolic reference PROMPTOPTION not resolved.

 

So I'm a bit stuck. Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Martin_Bryant
Quartz | Level 8

I've worked out a solution that whilst not entirely generic, I think is best one.

 

My prompt is CountryPrompt and by default nothing is selected. If the user leaves it this way, they I'm going to assume they want all countries. Too many to have selected initially and the most likely scenario is that the user wants just one, so this is the best solution for them. One is then easy to select. So my where clause is now...

 

where ....
and ( %_eg_WhereParam(Cust_Country, CountryPrompt, IN, TYPE=S) or &CountryPrompt_count. = 0)

This works because the prompt is from a static list and whilst CountryPrompt remains undefined, CountryPrompt_count doesn't.

 

It also saves faffing around with all of the macro stuff.

 

 

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

How does Enterprise Guide actually create the macro variables when nothing is selected?  Does it create the PARM macro variable?  What does it contain?  Does it create the PARM_COUNT macro variable?  What does it contain? Does it create any of the PARM0, PARM1, ... macro varaibles.

 

What code does the macro call generated when the parameter is empty?  Test it:

1929  %let mvar=;
1930  %let mvar_count=0;
1931  %put %_eg_WhereParam( varname, mvar, IN, TYPE=S );
0

So it looks like it emits a zero. Which is FALSE.

 

So test for empty value and skip the use of the macro in those cases.

%if %length(&parm) %then %do;
   where %_eg_whereparm(variable,parm,in,type=S) ;
%end;

If you cannot include the macro %IF code then just test if the emitted string is exactly 0.

where ( %_eg_whereparm(variable,parm,in,type=S) )
   or ( "0"= %sysfunc(quote(%_eg_whereparm(variable,parm,in,type=S))) )
;
Martin_Bryant
Quartz | Level 8

I've worked out a solution that whilst not entirely generic, I think is best one.

 

My prompt is CountryPrompt and by default nothing is selected. If the user leaves it this way, they I'm going to assume they want all countries. Too many to have selected initially and the most likely scenario is that the user wants just one, so this is the best solution for them. One is then easy to select. So my where clause is now...

 

where ....
and ( %_eg_WhereParam(Cust_Country, CountryPrompt, IN, TYPE=S) or &CountryPrompt_count. = 0)

This works because the prompt is from a static list and whilst CountryPrompt remains undefined, CountryPrompt_count doesn't.

 

It also saves faffing around with all of the macro stuff.

 

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 604 views
  • 1 like
  • 2 in conversation