| This post was kindly contributed by BI Notes for SAS BI Users - go there to comment and to read the full post. |
After some not-so careful consideration, I decided to take the SAS BI Content Developer exam. Some folks I know who have passed the exam recommended that I study how to work with prompts and stored processes. So I am making up examples and working through some exercise drills of my own creation (oooh … scary). I am noticing some of the features and nuisances of working with the Date Range prompts.
When I started using stored processes several years ago, it was not quiet this easy to setup date prompts, so the prompts are a welcome improvement. In this example, a Proc Tabulate summarizes the sales by location and date. The Date Range prompt allows the user to select a custom date range between Jan 1995 and December 1998.
This figure shows how the prompt looks and the results.
Check if the User is Insane …
I like this prompt because it does have some built in “sanity” checks. For instance, I tried to make the end date before the start date and it didn’t like it. You can see it generated an error message.
This capture is a little dramatic with a date of June 1900. If using Jan 1996 to Jun 1995 – the same error message appeared. These error messages help the user to not make mistakes that mess up how your stored process works. This saves you a lot of programming time trying to trap this issue in the code.
This is a really cool prompt – I selected the Date Type as Month and notice that the six macro variables are automatically created.
- Two have the default values as the beginning of the month (MONYR_MIN and MONYR_MAX)
- Two have the month end dates (MONYR_MIN_END/MONYR_MAX_END)
- Two labels (MONYR_MIN_LABEL/MONYR_MAX_LABEL)
I used four of the variables. The labels being available is a nice feature, so those were placed in the TITLE statement so the user knows what was selected. I know this screen capture is a little busy – I want you to see how the data and the prompt window work with the code.
- Data table: You must use a date variable for the prompt to work. In the code I used a BETWEEN operator. Note that MONYR uses the MONYY format for the values – but all of the dates are stored as the first of the month (01JUN1995, 01JAN1995). You have to know how the data looks so you can pick the correct macro variable.
- Prompt: Select the Date range prompt type and then use the generated macro variable as it makes sense in your code and data. Do a lot of date testing to ensure the user gets the expected answer. If this dates were (15JUNE1995, 31JUN1995) then using the MONYR_MAX=01JUN1995 would not return all of June’s sales. Is that what the user expects?
Out of curiosity, I tried a Date range prompt using a data table that had days instead of summarized month to see what differences there were. Of course the main difference is that the user can select the days she wants to view. This is handy; however, this table only goes to June 4, 2011 and the user is selecting September 30, 2011. Since there is not any error checking in the code, the STP generates an empty report.
Workaround: If there is a limit to the table date value, then it makes sense to pre-set the default. A table recreated daily may not have such a limit.
Another cool thing about this method is that the user can select relative values. If you have the user selections in the title then it assumes the user knows what she selected. I can see this leading to issues. As I noted above, the data does not go past June – so if the user selects the relative values – the title does not indicate the end dates.
Workaround: Add the actual date values or use those value alone. Not very sexy in this screen capture.
My thought is if the user does a screen shot of the report and gives to her manager a few days later – the report is more clear with the date available.
I’m studying – so hit me with some questions! I need to pass the exam! 
| This post was kindly contributed by BI Notes for SAS BI Users - go there to comment and to read the full post. |