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

More SAS Stored Process Sleight of Hand Tricks

Submitted by on 2014-01-29 – 7:23 AM
Thurston the Great Magician

From the Library of Congress

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 of Congress collection. I like that all of them have little devils working with them. I don’t know if that makes them more mysterious or scary.  Speaking of mysterious …  Angela and I are presenting a talk called “Debugging and Tuning Your SAS Stored Processes” at the SAS Global Forum in Washington DC on March 23.  You are invited to join us.

All You Have to Do is Control the Users

Ah, control the users? I think it’s easier to cut a woman in half or make a jet plane disappear than the control the BI user community.  So maybe we agree we cannot control them – but we can guide them to the right path. In the example stored process used in the last post, it allowed the users to make multiple selections and even no selection to get the data they wanted.

The problem with that logic is that it depends solely on the user to make at least one prompt choice. If the dataset is 1000 records, then maybe you don’t care if it returns all of the records because it is not that much data or burden to the server.  However, what if the dataset is more than 1,000,000 records?  Wow …. a magician, white rabbit, cape, and top hat still may not be enough to hold all that data! The real problem is that we don’t want to use system resources on stupid tricks.

Be Clever But Provide an Answer

Using the same macro, you can make a simple change to determine if the user made a selection and only continue if that is true.  Just count the characters in your &where_stmt. macro. If it’s still 3 characters (which is the length of “1=1”) you know there were not any additional values added and thus the user did not make a selection. Here’s how I updated the code, my changes are in green.


%macro BuildWhereStmt;
%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;
%put WHERE STMT IS:  &where_stmt.;
%if %length(&where_stmt) > 3 %then %do;
 title "Search Results";
 proc print data=candy.candy_sales_summary noobs;
 &where_stmt.;
 run;
 %end;
 %else %do;
   data msg; MSG="No variables where selected. Try Again."; run;
   title "Search Results";
   proc print data=msg noobs; run;
 %end;
%mend;
%BuildWhereStmt;

After the code traps that nothing happened to the variable, it alerts the user with a message. It would look something like this.

sas stored process with logic

Now you could have it do other things, perhaps just limit it to the first 100 rows or default to a set date. It’s possible the user is not sure how the data appears so doesn’t know what order number to use or if the name is the customer name or the company name.

Maybe Default to 100 Rows?

Here’s how to default to the first 100 rows using the OBS on the Options statement.  OBS instructs SAS to limit the data to the first 100 records. You can change the value to any number you want.  For instance, you could have obs= 32 instead of obs=100. In that case, only the first 32 rows would be pulled. [More about OBS in SAS doc set.]

%if %length(&where_stmt) > 3 %then %do;
 title "Search Results";
 proc print data=candy.candy_sales_summary noobs;
 &where_stmt.;
 run;
 %end;
 %else %do;

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

 

Maybe Default to Last 30 Days?

Here’s how to default to the last 30 days. Using the INTNX function you can change it to any amount of time.  So it could be the last year, last 3 days, or even last quarter. [More about INTNX from SAS doc set.]

%if %length(&where_stmt) > 3 %then %do;
 title "Search Results";
 proc print data=candy.candy_sales_summary noobs;
 &where_stmt.;
 run;
 %end;
 %else %do;
   title "Search Results - Last 30 days";
   proc print data=candy.candy_sales_summary noobs; 
      where date > intnx('day', today(), -30);
   run;
%end;

But There Still the Obvious Error to Discuss

In my next post I’ll talk more about a glaring error in this stored process logic and a simple method to overcome it. In the meantime, you can learn some card tricks from the Scam School.


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: