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 » BI Tools, Stored Processes

Which population method should I use for my SAS prompt?

Submitted by on 2014-04-13 – 9:31 AM

During our Debugging and Tuning SAS Stored Processes session, an attendee asked “Why would I choose a dynamic versus static prompt for my stored process?” It was a good question because the choice might make a difference in the stored process speed. Here’s some guidelines.

What are the different Population Methods?

Stored Processes take user input through a prompt to provide custom output.  Consider the following three prompts where the first one allows the user to type a value and the other two prompts provide a choices that are pre-populated. 

sas prompts

You can select from three different population methods: user, dynamic, and static.

  • User prompts ask the user to type their input, such as a number in the first example.
  • Static prompts populate from a pre-populated list created by the stored process author.
  • Dynamic prompts populate from a data set at run time.

When you build the prompt, you select your population method. Date prompts are a special in the sense that they can populate themselves, as discussed in the Using Date Range post.

sas prompts population method

When to Ask the User for Input

I think the user prompt is the most dangerous method for collecting input.  I’m not sure if there is anything worse than just allowing the user to go wild on your prompt and code. While Google can handle open queries with speed and charm, typically your stored process is looking for a specific answer. [I’m not the only person who thinks so.]

0414_prompts_06

I’m not saying to never use this prompt type, just make sure your code can handle when the value is misspelled, not found, or not even invalid.  Users have in common with wild animals if you think about it – some are pretty but most are unpredictable.

This post provides some tips for improving your prompts and this post series provides tips for writing better stored process code.

When to Use a Static Prompt

A static prompt allows the stored process developer to pre-populate the list. This is an obvious choice when the values rarely change. For instance, state names, country names, regions, company products, and so on.  It’s easy to edit the prompt so a quick addition or subtraction really only takes moments.

sas static prompts

The shared prompts that SAS provides are another example of static prompts and how to use them.

When to Use a Dynamic Prompt

If you have a frequently changing list, then a dynamic prompt is a lifesaver.  Take a list of customer accounts for example – if your company is any good at selling their product, then you expect this list to change and grow! The dynamic prompt queries the dataset for values when the user runs the stored process.  As shown in the figure below, basically a PROC SQL executes on the dataset.

sas dynamic prompts

Click for larger image

Wow … very handy if you want to ensure that the list contains the most recent customer added to the dataset.  The down side is when the dataset is really, really large.  In this case the user experiences a delay waiting for the stored process to populate the prompt so it can run.  This leads to user dissatisfaction and eventually back to your desktop to find a better solution.  I’ll provide some ideas for that situation in my next post.

Learn all about SAS BI

You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.

Never miss a BI Notes post!

Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.
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: , ,