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

Little Known Secrets from SAS Stored Process Magicians

Submitted by on 2014-01-26 – 4:57 PM

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 of the task.

Abracadabra! Write a Dynamic Where Clause

Bryan and I were creating a stored process where the user could select from different variables (such as customer name, customer ID, document ID, date document filed, and some other variables) or perhaps a combination of all. This may not seem tricky on the surface!  However, how do you write a where clause that allows the user to have a date range prompt, a numeric variable, and any other combination of choices?

Obviously you can create eleventy-million where clauses and add that many more IF/THEN statements to make it right.  This work requires someone who can pull a rabbit out of hat like Paul Dutton.  However, Bryan showed me a very simple (and elegant) solution that simply test for a value and adds it if necessary.  Hocus Pocus … it’s some Dynamic Where clause magic.

Prompts Without Requirements

If you notice in the following figure, none of the prompts require an answer. Our first issues is that if any of the variables are required then this method won’t work. While it is possible to say the user had to select at least once variable, which one do you select?  In our case it had to be wide open.  So Tip #1 – do not require any variables. [Three tips for improving your prompts]

sas stored processes multiple prompts

Code to Support Our Ideas

As the SAS BI Developer, you have a requirement.  Your where clause has to resolve even if the user selects nothing. If the code looks like the following and the &VariablePrompt is empty, your stored process will fail.  No one will be impressed with your Ninja coding skills.

      proc print data=mylib.mydsn;
      where variable = &VariablePrompt.;
      run;

So let’s write a macro that builds the where statement for us.  Just start with a where statement that will always resolve, such as 1=1. If the processor reads where 1=1 then the statement is true and it will continue.  Since we expect that a Where statement always retrieves a value from the dataset it may not occur to us as a solution right off – but it works.  So Tip #2 Create a resolvable where clause.  [Minimalist programming with stored processes]

sas stored process prompts

The next part of the solution is to simply build the where_stmt variable based on what other prompts were populated. It’s a simple test with a %length macro to determine if the macro variable is greater than 0. If so, then take the existing &where_stmt and add more to it.  If not, then keep moving.  Here’s an example using the SAS sample data called candy_sales_summary.


%macro BuildWhereStmt;
    %global where_stmt;
    %let where_stmt = 1=1;

    %if %length(&NamePrompt) > 0 %then %do;
     %let where_stmt = &where_stmt and name = "&NamePrompt"; 
    %end;

    %if %length(&OrderIDPrompt) > 0 %then %do;
     %let where_stmt = &where_stmt and OrderId = &OrderIDPrompt; 
    %end;

    %if %length(&DatePrompt_min) > 0 %then %do;
        %if %length(&DatePrompt_max) > 0 %then %do;
             %let where_stmt = &where_stmt 
                    and date between "&dateprompt_min"d and "&dateprompt_max"d; 
        %end;
    %end;
%mend;

%BuildWhereStmt;

   %put WHERE STMT IS:  &where_stmt.;


 title "Search Results";    
 proc print data=candy.candy_sales_summary noobs;    
   where &where_stmt.; 
  run;


Review the Log

After you run the stored process, you can review the log to see what happened. [Here’s my fancy log check program if you need one.] Here’s a snippet of the code.  You can see the user chose an order number and even with our 1=1 riding along – it worked out okay.

sas stored processes prompts

Hmm, what if the user doesn’t select anything and pulls back the entire dataset. Oh Noos!  I’ll talk about that in my next post.


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 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: , ,