BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » Stored Processes

Stored Process: Need to prompt based on the first prompt value?

Submitted by on 2011-09-29 – 1:30 AM 4 Comments

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.

Stored Process - Results

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:

  1. Edit the State prompt and go to the the Dependencies pane.
  2. Click the Add button to create a dependency.
  3. Select the prompt, which is Country.  

dependant prompts


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!

The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: ,


  • internet privacy says:

    Thanks for that awesome posting. It saved MUCH time 🙂

  • Ethel says:

    I frankly learned about nearly all of this, but never the less, I still thought it was helpful. Nice blog!

  • Anonymous says:

    Great review! You actually overviewed some curious things in this post. I came across it by using Yahoo and I’ve got to admit that I already subscribed to the RSS, it’s very great 🙂

  • Miroslawa says:

    Excellent information and facts! I have already been hunting for something similar to this for a long time now. Thank you!

1 Pingbacks »