BookmarkSubscribeRSS Feed
mrdlau
Obsidian | Level 7

I'm trying to understand what the %_eg_whereparam means in the context of trying to create a prompt in SAS EG

 

What does the syntax mean exactly?

For example, a code would be:

Select 
* 
FROM Table t1
WHERE %_eg_whereparam(people.co, Companies, IN, Type=S)

 

I know Type = S means string, but what about the first 2 positions?  Is it table and column?

Also, if I have this %_eg_whereparam, do not need a where statement.

 

For example:

Select
*
From Table t1
where
Companies = 'A'
AND %_eg_whereparam(people.co, companies, IN, Type = S
16 REPLIES 16
Reeza
Super User

https://blogs.sas.com/content/sastraining/2012/03/14/three_examples_of_egwherepara/

 

This is a good resource. The link to the paper is at the end if you're not a fan of videos.

Martin_Bryant
Quartz | Level 8

The videos seem to have vanished (for me at least) and the paper gives only limited information.

Does anyone have a proper piece of documentation for this? Something that fully defines all parameters and options.

 

I have to say that generally SAS documentation is the worst I've ever come across as a developer and I can't find anything at all for this particular item.

Martin_Bryant
Quartz | Level 8
Re: please explain %_eg_whereparam(....) for me.

https://blogs.sas.com/content/sastraining/2012/03/14/three_examples_of_egwherepara/

 

This is a good resource. The link to the paper is at the end if you're not a fan of videos.

Unfortunately, the videos appear to have been removed.

Reeza
Super User
The arguments for the _eg_WhereParam macro include the following:
1. Column, defines the evaluated variable
2. Parm, must equal the name of the Prompt
3. Operator, the condition of the variable to prompt evaluation. Valid options include:
Between, Not Between EG, =, NE, <>, IN, NOT IN
Note that when using between and not between, a range prompt is required or an inclusion of the
MAX= value.
4. Type, type of variable. Valid options include:
String or S, Date or D, Time or T, Datetime or DT
Default Value is String
Martin_Bryant
Quartz | Level 8

@Reeza and that's my point. According to the code that is linked here, there are more options that that. This is the macro header...

 

/* Build where clauses from stored process parameters */
%macro _eg_WhereParam( COLUMN, PARM, OPERATOR, TYPE=S, MATCHALL=_ALL_VALUES_, MATCHALL_CLAUSE=1, MAX= , IS_EXPLICIT=0, MATCH_CASE=1);

But these aren't explained anywhere and the code itself isn't commented so you have to really understand macros well in order to understand it.

 

Anyway hope this discussion helps others understand %_eg_whereparam better.

Reeza
Super User

Fair enough, but like I mentioned, one of the pluses for SAS is tech support and the ability to have that actioned and added to the docs. You can also post in the SAS Ballotware as a request as well.

Quentin
Super User

I think this is a fair critique.  SAS documentation is excellent.  But I do feel like with SAS 9 clients (EG, DI Studio, Stored Processes), SAS has added in a lot of macros to provide functionality (%_eg_whereparam, %stpbegin, etc) without always documenting them in the same way they would document a new PROC or STATEMENT.  Some of them you can't even find the source code for. My guess is many of these were designed to be used 'behind the scenes' by SAS developers, or used by SAS clients written by SAS developers.  It feels like they weren't really written with the expectation that us folks outside of SAS would start using them in our own code.  But, that was probably short-sighted.


If you search lexjansen.com for %_eg_whereparam, you will find papers written by the SAS community that explain it a bit more, including some by SAS employees, e.g. https://support.sas.com/resources/papers/proceedings11/309-2011.pdf

 

 

BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Reeza
Super User

Have you emailed tech support to ask by the way? They may have the source code or more documentation to provide?

One of the benefits of SAS is the ability to contact tech support.

sustagens
Pyrite | Level 9
%_eg_whereparam(people.co, Companies, IN, Type=S)

First argument is the column you want to filter.

Second is the name of the prompt (without the ampersand)

Third is the "IN" operator (this is used if your prompt has multiple values)

Fourth is the data type, in this case, string

Tom
Super User Tom
Super User

You need to look at the Enterprise Guide documentation as that is the SAS application that is defining that macro.  It is NOT part of the BASE (aka FOUNDATION) SAS product, so you cannot find the macro definition in the normal autocall libraries where SAS stores the macros it provides.

 

I found this page that seems to have the source code, not clear if it is up to date.

https://support.sas.com/downloads/package.htm?pid=2595

 

Martin_Bryant
Quartz | Level 8

Many thanks. It looks like there are no other options that I've missed then but I don't think I understand SAS code well enough to see exactly how to use this from the code (especially as it isn't commented at all).

 

I'll check the SAS EG docs again but I can't say I'm hopeful.

Tom
Super User Tom
Super User

The macro just emit a string of characters that look like a condition expression.

So to play around with it just use it in a %PUT statement.

%let mvar=XXX;
%put %_eg_WhereParam( varname, mvar, EQ, TYPE=S );

Results

varname EQ       "XXX"
Tom
Super User Tom
Super User

Note that in addition to not including comments the macro is not that smart.  If all of the macro variables that are created by the Enterprise Parameter tool do not exist it can generate invalid code and/or warning messages.

1921  %let mvar=XXX;
1922  %put %_eg_WhereParam( varname, mvar, IN, TYPE=S );
varname IN       "XXX"
1923  %let mvar=XXX;
1924  %let mvar_count=1;
1925  %put %_eg_WhereParam( varname, mvar, IN, TYPE=S );
varname IN ("XXX")
1926  %let mvar=XXX;
1927  %let mvar_count=2;
1928  %put %_eg_WhereParam( varname, mvar, IN, TYPE=S );
WARNING: Apparent symbolic reference MVAR1 not resolved.
WARNING: Apparent symbolic reference MVAR1 not resolved.
WARNING: Apparent symbolic reference MVAR1 not resolved.
WARNING: Apparent symbolic reference MVAR1 not resolved.
WARNING: Apparent symbolic reference MVAR2 not resolved.
WARNING: Apparent symbolic reference MVAR2 not resolved.
WARNING: Apparent symbolic reference MVAR2 not resolved.
WARNING: Apparent symbolic reference MVAR2 not resolved.
(varname IN (         "&mvar1",         "&mvar2"))

 

Martin_Bryant
Quartz | Level 8

@Tom wrote:

The macro just emit a string of characters that look like a condition expression.

So to play around with it just use it in a %PUT statement.

%let mvar=XXX;
%put %_eg_WhereParam( varname, mvar, EQ, TYPE=S );

Results

varname EQ       "XXX"

I'm sorry, but this doesn't help me understand which parameters I can add to the call though, does it? I need something to tell me what options I can pass and with which values and what those values mean. Maybe %_eg_WhereParam( varname, mvar, EQ, TYPE=S, JONNY=FISH ); is a valid option, but I'll never know unless I'm told somewhere.

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
  • 16 replies
  • 9506 views
  • 5 likes
  • 6 in conversation