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.
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.]
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.
Obviously 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.
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.