SAS Stored Process Dealing with Disappearing Results

This post was kindly contributed by BI Notes for SAS® Users - go there to comment and to read the full post.

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 looks and you can see code here.

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

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.  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 dataset 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;

	proc sql;
	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=candy.candy_sales_summary noobs;
    /*You can delete this:  &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;

%else %do;

options obs=100;
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 happens.  If no results are returned then &SQLOBS equals 0.  Either way I’m covered and the user understands what happened. Speaking of mysterious …  Angela and I are presenting a talk called “Debugging and Tuning Your SAS Stored Processes” at the SAS Global Forum 2014 in Washington DC on March 23.  You are invited to join us.

Post SAS Stored Process Dealing with Disappearing Results appeared first on BI Notes for SAS® Users. Go to BI Notes for SAS® Users to subscribe.

This post was kindly contributed by BI Notes for SAS® Users - go there to comment and to read the full post.