Little Known Secrets from SAS Stored Process Magicians
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]
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]
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.
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.
Latest posts by Tricia Aanderud (see all)
- Seven Interesting Data Storytelling Examples - 2018-06-09
- Use Network Analysis to Understand Your Customers with SAS Visual Analytics 8.2 - 2018-01-21
- My 7 Favorite Features in SAS Visual Analytics 8.2 on Viya - 2018-01-14
- Designing Dashboards: Finding the Fantastic Five Colors - 2017-06-19
- Creating a Web Analytics Report in SAS Visual Analytics 8.1 - 2017-06-19