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 » Stored Processes

SAS Stored Process: Dealing with Disappearing Results

Submitted by on 2014-01-31 – 8:14 AM 3 Comments
1636r_library_of_congress_Karmi_mystery_india

Image: Library of Congress

The world needs magicians or illusionist.  I love watching illusions.  When I was at the SAS Professional Forum in the UK, there was a illusionist who just knew one card trick after another. Even when he seemed to mess up, he would then point to the card box and say, “Oh your card must be in here.”  It was great because we watched him put the box aside earlier but we didn’t see him put the card in it. It was magic!

In this SAS stored process series I’ve shown you how to do your own magic tricks.  We started by learning how to create a dynamic where statement  and then if we did not get expected results how to recover and produce a result (oh the card must be in the box option). However, I said in my last post that there was a glaring logic error in my last example. Maybe you noticed it?

Not Everyone Likes a Disappearing Act

The dynamic where clause allows the user to select 3 things: customer name, order ID, and date range.   Here’s how the logic in a flowchart.

sas stored process logic fails

For the customer name prompt, I used a dynamic list so I knew that the company name was in the data so the user could not pick the wrong company. Also I added a start date so I know the user cannot select anything greater than the start date I indicated.  [More about bullet proofing your prompts.]

2014-01-31_prompt window

Ok, so here’s the catch – it’s possible the user can select one of these combinations and there is no data results to match it:

  • Company that ordered nothing in the selected date range
  • Order ID not in the selected date range
  • Order ID that doesn’t exist (maybe a typo) or in wrong format
  • Company and order ID mismatch meaning that order Id does not go with that company

Any of these choices would result in this result, which is missing.  Disappearing results really does make you a magician but leaves your user hanging.

sas stored process no resultsObviously an empty page is not the result we want and it. I’m sure the user will rerun the process a few times thinking they had a typo – but before long this stored process will be back on your desk because it looks more like a coding error than a user caused error.  So much for the disappearing rabbit.  I think we now need to use a let-me-guess-which-card-you-are-holding trick. [Need to see the log when the stored process didn’t return a result?]

Let’s Count the Cards, I mean Rows

Just like any good magician we need to be sure of the outcome before performing the trick. Our logic above presumes the user will provide the right data, but what if they didn’t? It would be great if we somehow knew if the &where_stmt would produce a result and then act on that result. We actually can with PROC SQL and its special output called &SQLOBS.  This is an automatic variable that contains the number of observations in the data set after execution. [More about SQL Automatic variables.] So we could pass our &where_stmt to PROC SQL and then determine if there was a result.

Here’s a simple modification to how our updated logic might look.  The main point is capturing the SQLOBS and acting.  However, this could be expanded so we return a result if the user did not make any prompt choices, which was our original issue.

example_sas_stp_logic_fails_flowchart Take 2

Here’s the Secret to the Trick

Here’s how I would modify the code.  I’m sending an error message but you could send 10 example rows if you felt like it to help the user understand the needed formats.


%macro MakeReport;
%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;

/*Use the where_stmt in the SQL code to determine if the where statement makes sense*/
proc sql;
 create table work.Result as
 select * 
	from candy.candy_sales_summary
  &where_stmt.
	;
 quit;

%put SQLOBS IS:  &sqlobs.;
%put WHERE STMT IS:  &where_stmt.;
%if %length(&where_stmt) > 3 %then %do;

  %if %length(&SQLOBS) > 0 %then %do;
    title "Search Results";
    proc print data=work.result noobs;
        /*You can delete this:  &where_stmt. */
    run;
  %end;
  %else %do;
    data msg; MSG="No variables were selected. Try Again."; run;
    title "Search Results";
    proc print data=msg noobs; 
    run;
%end;

%else %do;
 options obs=10;
  title "Search Results - First 100 Rows Only";
  proc print data=candy.candy_sales_summary noobs; 
  run;
%end;

%mend;

%MakeReport;

I placed the PROC SQL statement under the dynamic where clause and wait to see what the user selects.  If no results are returned then &SQLOBS equals 0.  Either way I’m covered and the user understands what happened.


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

3 Comments »

  • What a great suggestion! And can you believe that I managed to find a SASGF14 paper too? No magic here… https://sasglobalforum.activeevents.com/connect/sessionDetail.ww?SESSION_ID=1247

  • Tricia says:

    Someone should write a GloFlo paper about system resource usage and other topics that could be shown on a dashboard! 🙂

  • AHA! The mystery unfolds from your previous blog post…

    You mention above that if the user gets a blank page returned they may rerun the process thinking they had a typo. So without validation, as you’ve shown, there is the potential for more system resources to be unnecessarily consumed on your SAS server as the user reruns the process.

    If there are 100 users running the stored process at some point in a week and 20% of them had mis-specified the prompt values, imagine the amount of system resource usage that can be saved, especially if working with large tables and number crunching stored process code.

    No illusions on the benefits gained by adding data validation. Great tip!