04-05-2012 07:25 PM
I am trying to set up an EG project where Query Builder is used to subset the data. There are a lot of standard prompts and I am using the prompt mananger for that.
However, one of the prompts (a date range) is optional. The user wants to be able to not specify values in which case the query would not have a date range where condition; but if they do specify values, it would use the appropriate filter condition.
I just assumed that the _eg_whereParam macro was smart enough to check the values of the associated macro vars before creating the appropriate where clause snippet. But I am seeing syntax errors because ''d is not a valid SAS date value.
Is this something that _eg_whereParam can't do?
04-05-2012 07:42 PM
The _eg_whereParam should be able to cover date ranges. The issue might be the type of condition that you are selecting.
When using a date range prompt, choose the operator 'In a range'. Then the value field should only contain the prompt name.
Included is a screenshot of this from my SASGF11 presentation last year.
04-05-2012 08:01 PM
I've succesfully used date range prompts in EG. I guess I was not clear enough in my question. Unlike my prior examples, in this case the date range is an optional prompt as the user wants to be able to run the query both with and without the date range. Based on my testing, it seems that the macro is not detecting that the prompt parameters are null and thus not specified.
Since prompts are not always required, it seems to me that the macro should handle that. So another way of putting my question is whether _eg_whereParam can only be used with required prompts?
04-05-2012 08:30 PM
I was able to duplicate this in eg5.1. I guess that the macro was built with the thought that the user was only applying it when they wanted to use it?
If you look at the code construction in the 'Preview' pain, if the eg_whereparam returns no values at all the 'AND <blank>' would also cause the SQL code to fail for other reasons.
FROM CANDY.SALESDETAIL2011 t1
WHERE %_eg_WhereParam( t1.Product, product, EQ, TYPE=S, IS_EXPLICIT=0 ) AND %_eg_WhereParam( t1.Date, daterange,
BETWEEN, TYPE=D, IS_EXPLICIT=0 );
Wondering if you could set up a conditional processing step before this query task to fill with the date prompt values with beginning/end of time dates if nothing is entered.
Then in the query task itself, use the &daterange_min and &daterange_max values instead of the _eg_whereparam macro.
04-05-2012 10:05 PM
It does not look like the conditional processing is going to work for this set of users/reports. I think it requires (from the user's perspective) too many counter-intuitive/extra steps for it to work. And it will also require extra passes of the data.
Since this set of users has a fairly standard set of prompts and I've had to introduce the concept of macros and macro variables to generate their title text, I will present them with a couple of options (including):
In the meantime perhaps I will open up a track with TS and suggest that the _eg_whereParam macro should be upgraded to handle this scenario.
04-06-2012 11:03 AM
Originally, the %_eg_whereParam was developed for use only in the auto-generated code that you use in SAS Enterprise Guide queries and project prompts, allowing those to be "transcribed" into Stored Process prompts when you create stored processes from your EG project flows.
The contents of the macro contains elements that have been developed over many years, probably even back to the days of SAS/IntrNet development!
Of course, like many elements of SAS, this macro has evolved to serve more than its original purpose, as customers find that it's "just the thing" to solve their own programming challenges (or in your case, "almost just the thing").
I think that your suggestions are useful and valid. Just keep in mind that as new variations are considered, I expect the R&D staff will incorporate only those changes that can keep the original purpose/behavior intact.
04-06-2012 11:16 AM
Thanks for the comments Chris. In the use case I am dealing with now, the macro is only being used in the Query Builder task. It is not being copied/modified/converted to a stored process. Thus, I hope that R&D can implement something like this for use within the EG environment. It seems to me (he says naively) that it could be done fairly simple by just generating:
for those cases where the prompt macro variables are null.
However, your comment to stored processes triggered an idea that I need to try out. The conditional prompt could not do what I needed, but it would be simple, I think to write a stored process with an optional prompt (or maybe just a code node that calls an application specific autocall macro) that does nothing if the prompt is not specified, but that subsets the data and uses the same name for the output data set if the prompt is specified. I am going to test that out and will reply again if that works. Seems that it should as I can use &_syslast to get the name of the input data set.