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
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.
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.
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 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.
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.
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
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.
%_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
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
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.
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"
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"))
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.