Tips:Check if a WHERE clause is valid

From sasCommunity
Revision as of 16:16, 8 February 2017 by Paulkaefer (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
/* 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 nosyntaxcheck;
data _null_;
    call symput('whereClauseStatus','No Rows Returned');
    set sashelp.class;  /* replace with an appropriate data set*/
    where &clauseToCheck;
    call symput('whereClauseStatus','Rows Returned');
    stop;
run;
 
/* restore the options */
options &saveOptions;
 
%put NOTE: whereClauseStatus is &whereClauseStatus;

This sample code illustrates a simple technique to test whether a where clause is valid syntax and whether it returns any rows. Such tests are useful in applications that must accept a where clause as a parameter or option.

Note the following about this example:

  • It saves the current values of options that need to be reset to run the test.
  • The value of the whereClauseStatus macro variable gives you the results:
    • A value of Likely Invalid Syntax means there was an error - most likely a bad where clause.
    • A value of No Rows Returned means the syntax was valid but returned no rows.
    • A value of reason Rows Returned means the syntax was valid and returned at least one row.
  • Other return values could be used (e.g., -1, 0, 1) - it depends on how you plan to use the results of the where clause check.
  • Try this out for yourself:
    • An invalid where clause: %let clauseToCheck = sex = M;
    • Valid syntax that returns no rows: %let clauseToCheck = sex = 'x';
    • Valid syntax that returns rows: %let clauseToCheck = sex = 'M';
Submitted By Don Henderson (talk)