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:
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!