BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidPhillips2
Rhodochrosite | Level 12

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

5 REPLIES 5
Reeza
Super User

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
Quartz | Level 8

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
Opal | Level 21

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
PROC Star

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
Rhodochrosite | Level 12

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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2274 views
  • 6 likes
  • 5 in conversation