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.
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.
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 an SQL view is created, then SQLOBS contains the value 0.
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
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.