topic Re: please explain %_eg_whereparam(....) for me. in SAS Programming

please explain %_eg_whereparam(....) for me.

mrdlau — Fri, 16 Mar 2018 23:22:49 GMT

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

Re: please explain %_eg_whereparam(....) for me.

Reeza — Sat, 17 Mar 2018 01:48:28 GMT

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.

Re: please explain %_eg_whereparam(....) for me.

sustagens — Wed, 28 Aug 2019 07:22:39 GMT

%_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

Re: please explain %_eg_whereparam(....) for me.

Martin_Bryant — Fri, 15 Jul 2022 14:51:46 GMT

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.

Re: please explain %_eg_whereparam(....) for me.

Tom — Fri, 15 Jul 2022 15:03:48 GMT

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

Re: please explain %_eg_whereparam(....) for me.

Martin_Bryant — Fri, 15 Jul 2022 15:25:58 GMT

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.

Re: please explain %_eg_whereparam(....) for me.

Tom — Fri, 15 Jul 2022 15:38:38 GMT

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"

Re: please explain %_eg_whereparam(....) for me.

Tom — Fri, 15 Jul 2022 15:44:36 GMT

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"))

Re: please explain %_eg_whereparam(....) for me.

Martin_Bryant — Mon, 18 Jul 2022 07:17:23 GMT

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

Re: please explain %_eg_whereparam(....) for me.

Martin_Bryant — Mon, 18 Jul 2022 07:19:09 GMT

Re: please explain %_eg_whereparam(....) for me.

Posted 03-16-2018 09:48 PM (5494 views) | In reply to mrdlau

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.

Re: please explain %_eg_whereparam(....) for me.

Reeza — Mon, 18 Jul 2022 17:27:35 GMT

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

Re: please explain %_eg_whereparam(....) for me.

Reeza — Mon, 18 Jul 2022 17:33:51 GMT

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.

Re: please explain %_eg_whereparam(....) for me.

Quentin — Mon, 18 Jul 2022 18:27:53 GMT

My guess is you will have to resort to reading the macro definition.

Re: please explain %_eg_whereparam(....) for me.

Martin_Bryant — Tue, 19 Jul 2022 08:14:11 GMT

Yes. I've looked it over and got what I need, but I don't claim to fully understand it at all.

Re: please explain %_eg_whereparam(....) for me.

Martin_Bryant — Tue, 19 Jul 2022 08:37:09 GMT

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

Re: please explain %_eg_whereparam(....) for me.

Reeza — Tue, 19 Jul 2022 15:29:27 GMT

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.

Re: please explain %_eg_whereparam(....) for me.

Quentin — Wed, 20 Jul 2022 13:18:16 GMT

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