Check If a Value Queried Contains a Value

[DavidPhillips2]

Is there a way in SAS 9.2 to write a check to see if a value queried contains a value?  In this particular case I am dealing with numerical values. E.g.

SELECT total into :max_level_total

From enrollment;

The step after this I tried dividing my numerical blank value by another numerical blank value and got an error.  This occurred when my query returned 0 rows.  I would like to include an if statement to handle this.

A flag variable for if the query returned rows or not would also solve what I need.

[Reeza]

Use the automatic macro variable SQLOBS?

SAS(R) 9.2 SQL Procedure User's Guide

SQLOBS

contains the number of rows that were processed by an SQL procedure statement. For example, the SQLOBS macro variable contains the number of rows that were formatted and displayed in SAS output by a SELECT statement or the number of rows that were deleted by a DELETE statement.

When the NOPRINT option is specified, the value of the SQLOBS macro variable depends on whether an output table, single macro variable, macro variable list, or macro variable range is created:

• If no output table, macro variable list, or macro variable range is created, then SQLOBS contains the value 1.
• If an output table is created, then SQLOBS contains the number of rows in the output table.
• If a single macro variable is created, then SQLOBS contains the value 1.
• If a macro variable list or macro variable range is created, then SQLOBS contains the number of rows that are processed to create the macro variable list or range.

If an SQL view is created, then SQLOBS contains the value 0.

[billfish]

Perhaps this may help.

/*******************/
/*** sample data ***/
/*******************/
data t_a;
input a1 @@;
cards;
0 1 2 2 7 8 9 6
;
run;

/*********************************************/
/*** a PROC SQL query which deals with the ***/
/*** possibility that the denominator = 0  ***/
/*********************************************/
proc sql;
   select
      (case when sum(10<=a1<=12) = 0
            then 999.99
            else round(sum(2<=a1<=5)/sum(10<=a1<=12),01) end),
      (case when sum(10<=a1<=12) = 0
            then 'Y' else 'N' end)
      into :m_test, :zero_denom
   from t_a;
quit;

%put testing macro variable m_test &m_test;
%put testing macro variable zero_denom &zero_denom;

produces in the log file:

testing macro variable m_test   999.99

testing macro variable zero_denom Y

[Patrick]

You could initialize the macro variable with a '.' (a missing). If 0 rows returned then it will remain missing else it will get the value from your query.

%let max_level_total=.;

proc sql noprint;

  SELECT age into :max_level_total

  From sashelp.class where name='xxx'

;

quit;

%put max_level_total: &max_level_total;

Not sure if the code you've posted is representative for your real code. In your example your query could return multiple rows. Only the last value would remain in the macro variable.

[Astounding · Accepted solution]

To answer your original question first, here's the way I prefer to see whether a macro variable has a non-null value:

%if %length(&max_level_total) %then %do;

An entire paper has been written comparing the methods, so this is just my preference.

Just as important, you may need to understand the behavior that you are noticing.  When SQL extracts data into a macro variable, it leaves the macro variable unchanged when 0 rows are selected.  That's different than when SQL extracts a statistic (such as SUM) into a macro variable.  Also note that SQL does not reset macro variables when extracting 0 rows.  So if you run the same code twice in the same program, and only the first instance returns rows, the macro variable remains the same after the second instance.

Good luck.

[DavidPhillips2]

The built in SQL count methods are efficient.

I was running similar code twice where I had an insert into in one block and called the same insert into
in a later block which would run into the scenario that Astounding described.  It may be a good practice to use a different
variable name in the second insert into block.