headline »

sas dynamic prompts

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 …

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 »

Working with Large Prompt Population Lists

April 21, 2014 – 7:38 am | 2 Comments
5a46377r library of congress truck overturn

Source: Library of Congress

In my last post, I warned you about hooking up your huge dataset to a dynamic prompt.  Impending disaster, user unhappiness and worst of all – rework!  Ahhhh the horror of it all!!!

I wish I knew a way to tell you when the dataset was too large for the dynamic prompt, but it is really dependant on your system.  There are a lot of factors that influence stored process speed such as hardware, network, and even if the data lives in a database. Here’s a some alternate strategies for working with larger datasets in your dynamic prompt.  [Quentin provided a tip for working with selection list in this post.]

For these examples I’m using the Consumer Complaints dataset that I downloaded from the Consumer Protection Bureau to use as example data.  I like this dataset because it has its own built-in drama.  What were people complaining about? Kinda like when you see a car accident and you know it might be gross but you can’t stop yourself from looking?  Yeah that.

So far in 2014, there are two credit reporting agencies in the top 3 for the most complaints logged.  Congratulations!

Use a Pre-Built Tiny Dataset

You already know that the dynamic prompt runs code to get the list as shown in the last post.  One way around the larger dataset is to create a smaller pre-filtered dataset for the prompt to use based on the larger dataset. Then use that dataset to populate the prompt, thus eliminating most of the work the prompt needs to do at run time.

In this example, I have a CONSUMER_COMPLAINTS2 dataset with a gazillion company names listed. So I created a second dataset called CONSUMER_COMPLAINTS_TINY that just contained a distinct list of the companies. When you build your prompt, just use the smaller dataset as the data source. Since both datasets contain the same values, it shouldn’t create an issue for the stored process.

smaller dataset in prompt

Use a Dependant Prompt with ABCs

Another problem with a long list is that is takes the user forever to sift through it to find the company they want.  Probably if the company name starts with an A or Z it’s not that bad – but if it’s in the middle of the alphabet it takes forever. You can use a dependant prompt to remedy the situation.

In this example the first prompt contains the letters of the alphabet and the second prompt only pulls the names of the values that start with that letter (see below).  I used a static prompt and a dynamic prompt.

sas dependant prompt

Preparing the data

In the base dataset I added a second column that just contained the first letter of the company name. I also did a special column for those companies that contained a digit.

prompt dataset example

Adding a Static Prompt

Use a static prompt populated with the first alphabet character.  I just typed the letters myself as shown in the example below.  Notice I also added the Number value for those companies whose names start with a digit.

sas static prompts

Click to see a larger image

Adding the Dynamic and Dependant Prompt

I created a second prompt that is dynamic.  It can contain the smaller list shown in the first tip or just be an ordinary dynamic prompt. However the trick is to add the dependant prompt.  Here’s an example of how I did that. [Check this post for more specific steps.]

dependant prompt


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!


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!

Building a SAS Stored Process Log

February 24, 2014 – 7:14 am | 9 Comments
Stored Process Log

As a SAS stored process developer, a question sometimes pokes its way into my head: “Are people using the stored processes I write?”  In fact, really I have four questions:

What stored processes are being used? 
Who is using them?
When are they being used?
How are they using them?

I realized what I’ve been missing.  I need a SAS stored process log.
If It Works for a Macro…
As a macro programmer …

SAS Add-In for MS Office: Abe Lincoln Sends Glee in a Snowstorm

February 18, 2014 – 6:54 am | Comments Off
lincoln loves sas

It’s been really snowy and cold in North Carolina for the past week.  About 5″ of snow fell in 2 hours, which unleashed pandemonium. Power was out for some, flights were cancelled because the airport shutdown, and many people even left their cars beside the road electing to walk home or stay in a hotel for the night. Since I work from home, it was …

#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 …

More SAS Stored Process Sleight of Hand Tricks

January 29, 2014 – 7:23 am | Comments Off
Thurston the Great Magician

In my last post, Little Known Secrets from SAS Stored Process Magicians, I talked about how SAS developers are often confused with magicians.  I’m guessing it’s not our top hat and disappearing rabbits – more likely that SAS allows us to do all kinds of tasks that seem impossible but are undeniably easy.
Note: This series was inspired by the magician posters I found in the Library …

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 …