Using &SQLOBS to Report on Empty Sets

From sasCommunity

When an SQL query returns zero rows, the log will display

NOTE: No rows were selected.

and no report is produced. What if you need a message not in the log but in the output window (or listing file, or other ODS destination)? The automatic macro &SQLOBS makes that rather easy.

Here is an example:

proc sql;                                             

title 'Ages of Boys Taller Than 68 Inches';           

select name, age, height
 from sashelp.class
 where sex = 'M' and height > 68;                     

select ' ' label = "Subset is Empty"
 from sashelp.class
 where not &sqlobs;

The first SELECT statement produces this report:

Ages of Boys Taller Than 68 Inches                    

   Name           Age    Height
   ----------------------------
   Alfred          14        69
   Philip          16        72

Because there are two rows in this result, &SQLOBS receives the value 2. This is used in the WHERE clause of the second SELECT statement, and the negation (NOT) results in a value of FALSE, so no rows are selected and the statement produces nothing in the output window.

Now consider the equivalent code for girls:

title 'Ages of Girls Taller Than 68 Inches';         

select name, age, height
 from sashelp.class
 where sex = 'F' and height > 68;                    

select ' ' label = "Subset is Empty"
 from sashelp.class
 where not &sqlobs;                                  

quit;

THe first SELECT statement produces nothing in the output window because the HEIGHT condition filters out all rows. Thus, &SQLOBS receives a 0 (zero). When negated in the WHERE clause of the second SELECT statement, the result is TRUE and all rows are admitted. As a result, the output window displays:

Ages of Girls Taller Than 68 Inches                  

               Subset
               is
               Empty
               ------

Note that the use of SASHELP.CLASS in the second SELECT statement of each example is arbitrary. Any table will do, as long as it is certain to have at least one row. If it has too many rows, it will generate unwanted redundant pages.