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

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

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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

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