The date prompt from SAS Prompt Framework provides the options of single selection or a range, however what if you need to select multiple individual dates such as Monday (May 28), Wednesday (May 30), and Friday (June 1) of last week? There are a few alternatives to accomplishing this.
Option 1. Use a Text Prompt Instead of a Date Prompt
Set up a text prompt to select one or more values from a data source. The data source for the prompt would then be the date values represented within your data source - BUT FORMATTED AS CHARACTER. Of course, if this is a really large data source it would be better to use a permanent data table that is updated during your ETL processes with the sorted list of distinct dates.
Included is an example from SAS Information Map Studio.
1. Create a custom data element that reformats date into text. Use the custom expression to leverage the PUT statement as seen below.
In this example, the code is:
put(<<PRDSAL3.DATE>>, mmddyy9.)
2. Use this new data element to create a prompted filter. As seen below, I have selected the dynamic list option (#1) and allowed users to select multiple values (#2). Then I used the custom data element DateText (#3) to populate the list itself.
When I test the information map prompt, I am now allowed to select multiple individual dates.
Option 2. Leverage a date dimension from an OLAP Cube
Rather than having the data source as a flat table, place it within an OLAP cube with the date as a dimension. Then the prompt will allow you to select one or more individual date values (if you select "Multiple values" for the Number of values drop down box as seen below).
Included is a screenshot of the resulting dimension filter from a test in SAS Information Map Studio.
Do you have any other tricks to getting prompts to operate as required but not readily available 'out-of-the-box'? Please comment & share!
Also, the prompting framework is an entire chapter in the book Building Business Intelligence Using SAS: Content Development Examples. If you buy the e-book to you can learn more about this cool functionality today!
3 Comments
I want to give user a prompt which is based on MONYY format. Where user can select a Multiple values. When I create a date prompt by converting it in text form , I am getting value in Asceding order i.e April Month coming first for all years and so on.
Then I, changed the prompt format to yyyy-mm, so first problem got solved that it is coming in order of latest to older.
Now challenge is I want to show the default to current month in portal. and if user launch a live report he will be able to select a different dates but not sure how...
Help....
I would like to have my date prompt default to a date dynamically defined by the current date, e.g. the last day of the last complete month. Is there a way within the prompting framework to derive the default value from an expression. The options SAS provides (n days ago, current day of last month etc.) are not sufficient.
Thanks
Hi Matt,
Not directly. What I would suggest as a workaround is a bit complicated but here are the pieces:
1. Create a date prompt with no default applied.
2. Create a single data table or view that has the derived value from your expression. Register in the metadata.
3. Create a second date prompt that allows users to select a date value from a dynamic list. Select the data table created in step 2.
4. On the map filter use both of these prompts with an OR operand. Then on the report both prompts will be displayed.
Let me know if that makes sense & how it pans out!
~ Angela