headline »

Wanted! SAS Rustlers Unite at #SASGF15 #TweetUp

February 22, 2015 – 12:23 pm |

In April 2015, SAS users travel across the world to the SAS Global Forum in Dallas, Texas – yes the legendary Wild West.  If you are arriving early at the conference (maybe for a workshop or to take a certification exam) – then you’re invited to a special and exclusive event.

#SASGF15 Tweeps Unite!
This is an informal, pre-conference gathering for tweeps on Saturday 25th April at 8 PM at the conference hotel. …

Read the full story »
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

BI Tools, Stored Processes »

Which population method should I use for my SAS prompt?

April 13, 2014 – 9:31 am | Comments Off

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


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

Get the latest BI Notes post in your Inbox when a new post is released! Click here for free subscription. You email address is kept private and you can unsubscribe anytime. Go ahead ... join us!

SAS Office Analytics: Working with Your Inner SAS Programmer

March 12, 2014 – 8:00 am | Comments Off

When working with new or even casual SAS BI users, the SAS Office Analytics really bridges the gap between “I’m not a SAS programmer” and “I have 1000’s of ideas for data visualizations”. Users need an easy way to access the data warehouse and create reports or answer simple questions. The SAS Office Analytics package (aka SAS Add-In for Microsoft Office) a natural choice since …

#SASGF14: You’re Invited to a TweetUp!

February 10, 2014 – 6:50 pm | 2 Comments
2014 sas global forum tweetup

Hello, G’Day, Hola, Hej, Bonjour, Hallo! In March 2014, SAS users travel across the world to the SAS Global Forum in Washington, DC.  If you are arriving early at the conference – then you’re invited to a special and exclusive event.

Tweeps Unite!
This is an informal, pre-conference gathering for tweeps on Saturday 22th March at 8 PM at the conference hotel. Yes, a #SASGF14 #TweetUp  for people to meet their followers face-to-face …

Little Known Secrets from SAS Stored Process Magicians

January 26, 2014 – 4:57 pm | Comments Off
0262r library of congress laurent magician

When writing stored processes – it can be tricky designing the perfect prompts especially when working with tricky users. Maybe you feel more like a magician than a SAS developer. Recently I needed a SAS stored process that allowed a user to find a needle in a very large haystack.  Let me share a simple trick I learned from Bryan Stines to make simple work …

SAS Visual Analytics: Tips for Unriddling Encoding in SAS Visual Analytics 6.3

January 20, 2014 – 6:00 am | 2 Comments
Batman's Riddler

In preparation for Tricia and my joint paper at SAS® Global Forum 2014, “SAS Admins Need a Dashboard Too” our SAS admin support team, Ken Aanderud and Paul Homes  had been busy setting up our new SAS Visual Analytics 6.3 environments, which we are very excited about! As we got underway loading tables to explore and analyze data we came across an error that prevented a table to …

Administration: Retrieving Passwords from the SAS Metadata

January 13, 2014 – 8:11 am | 2 Comments

When working with the SAS metadata, sometimes a developer needs to retrieve a user ID and password from the metadata. For instance a developer may need the encoded password to connect to the database using the SAS pass-through with relational databases like Oracle and TeraData.  [More discussion about the pass-through :  here with TeraData, here compared to Libname, and here in SAS 9.2 doc set.]
As …