Stored Process: Need to prompt based on the first prompt value?
Prompts that use other prompts to get the values have plenty of names: conditional prompt, dynamic prompt, dependent prompt, and even smart prompts. These prompts make it so easy for your users to interact with your stored process. For instance, let’s say your first prompt asks the user to select a country and the second selects a city. You would not want the user to select France and then be able to select Los Angeles. Your stored process most likely would not find a value, but even worse imagine all the scrolling the user has to do.
What you want is for the user to select the country and then only see cities that are in that country. Thus the second prompt has a dependency on the value of the first prompt. Here’s how to use them. [Here’s some other tricks for working with prompts.]
Coding a Dependent Prompt
For this example – I am creating a report where I want the user to only have the certain values available. If the user selects Mexico as the country then I only want states within Mexico to be available. This pictures shows you how I have the stored process code setup – nothing special. [You can try this at home kids!]
When the process is executed, you are prompt for country and state. State does not populate until the user selects a country. Then the report is generated to show you the sales for that country and state.
What’s the secret?
As you can see in the example below, I have created two prompts to support the code: Country and State. I want the state values to be filtered based on the Country the user selects. Both of these prompts were created as text prompts.
The trick is to help the prompt understand it is dependant on the value of another prompt.
So I would do the following:
- Edit the State prompt and go to the the Dependencies pane.
- Click the Add button to create a dependency.
- Select the prompt, which is Country.
Working with Information Maps
The dependant prompts work similarly in the other SAS BI tools. However, if you are creating the cascading prompt in Information Map Studio, check out this “My cascading filter does not work” post to avoid a common blunder!
Learn More about SAS Stored Processes and Prompts
You can learn more tips and tricks for creating, debugging, and using SAS stored processes in the 50 Keys to Learning SAS Stored Processes book. It's a complete guide to SAS stored processes. Check Amazon for best pricing and quick shipping!
Latest posts by Tricia Aanderud (see all)
- Designing Dashboards: Sending Your Style Vibe - 2017-01-21
- SAS Visual Analytics: Design Versus Reality - 2016-10-05
- Seize the Day! Submit an #SASGF Abstract - 2016-09-21
- Need a Dynamic X-Axis with Your SAS Visual Analytics Report? - 2016-07-31
- Planning Your SAS Visual Analytics Dashboard - 2016-06-06