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.
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.
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.]
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.
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.
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.
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.