Tips Talk:Check if a WHERE clause is valid
Interesting tip. Here are some suggestions to improve it.
- It took me some time to work out what was going on, so I think a read more link to a longer explanatory page would help (inexperienced but) interested readers (like me).
- The line gave a syntax error with the and option. I couldn't find the and option in the manual, so I think it is a bug. I noticed that and is not used in the %LET statement that populates the saveOptions macro variable.
options noerrorabend and nosyntaxcheck;
- Have you considered that with a few small changes this code could even be written as a self-contained macro like?
%macro WHEREclauseCheck(DataSetName,clauseToCheck) ; /* set the default for the status of the where clause */ %let whereClauseStatus = Likely Invalid Syntax; /* save the current option settings */ %let saveOptions = %sysfunc(getoption(errorabend)) %sysfunc(getoption(syntaxcheck)); /* reset the options for the test */ options noerrorabend /* and */ nosyntaxcheck; data _null_; call symput('whereClauseStatus','No Rows Returned'); set &DataSetName ; /* replaced sashelp.class with an appropriate macro variable for data set name*/ where &clauseToCheck; call symput('whereClauseStatus','Rows Returned'); stop; run; /* restore the options */ options &saveOptions; %put NOTE: whereClauseStatus is &whereClauseStatus; %mend WHEREclauseCheck;
The and was a cut and paste error. Now fixed. This requirement came up on a project I was working on yesterday and so I emailed a summary of how I had done this before and in the process of testing it and then combining the email and the code, I missed the and.
WRT the issue of a packaged macro, I thought about that. But I was trying to keep it simple. For example, in a packaged macro you would want to control the three return values, probably pass in the name of the macro variable for the return code. But maybe that could be listed as suggested enhancements on a page with more details (and perhaps logs of the multiple runs).
good tip --Art T 10:00, 9 March 2015 (CDT)
Don: I think it's useful as is and that providing a macro isn't necessary for the tip. One could always write a paper offering a macro and that could be linked to the tip. However, if someone does, I would hope that it uses named parameter. I, personally, would prefer that someone captured it in a function.
I'm not sure about whether a more detailed explanation would help or detract. --Art T 12:24, 9 March 2015 (CDT)
I agree that the article is now good enough for a tip.
But I think that this sort of tip could be a launching point for a more in depth article (or two) about how to implement these sorts of tests, or how to package and implement macros, either as a macro function or as a user defined function, or explain the programming techniques used and why they work in SAS. I just saw so much programming experience had been packed into the code that I thought it wanted further explanation. I always believed this should be done elswhere, not in the tip, but I didn't want to limit my imagination about where it could lead. - Cameron (talk) 22:32, 9 March 2015 (CDT)