Inconsistent results by SQLOBS depending on NOPRINT option in PROC SQL

[jffeudo86]

Hi there,

I came across SQLOBS and tried some codes but the returns are inconsistent (from the logs). 

PROC CONTENTS DATA=SASHELP.cars
OUT=CarVars
NOPRINT;
RUN;

PROC SQL NOPRINT;
SELECT *
FROM CarVars;
QUIT;

%PUT &=SQLOBS;

Above code shows from the log:

83 %PUT &=SQLOBS;

SQLOBS=1

If however I remove the NOPRINT option from the proc sql, I get a different result, one that I expect the NOPRINT option should return

PROC SQL;
SELECT *
FROM CarVars;
QUIT;

shows the ff from the log:

83 %PUT &=SQLOBS;

SQLOBS=15

Of course I could also do a select into variable, but just wanted get clarification or guidance on why sqlobs returns different results.

[novinosrin · Accepted solution]

from the docs

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.

Note: The SQLOBS automatic macro variable is assigned a value after the SQL SELECT statement executes.

[jffeudo86]

That makes sense now. Thank you!

[mansour_ib_sas]

must I understand, that there must be an output (even print) for the sqlobs macro variable contains the value?

[Reeza]

Depends on what you call an 'output'. Macro variables creation will still work. I think the key is, what causes SAS to not read/process the whole data set, because in those cases it won't have the N available in SQLOBS. But SAS stores the metadata such as type/format/informat and count for every table and every column in every table in the dictionary tables.

[mansour_ib_sas]

precisely, to have the N, you need a data output and print from what I just understood, is considered by sas as an output as well as a table creation.

[Reeza]

To have the N in SQLOBS, yes it has to process the data. But that's really only useful as a step in your process to save you from having to separately calculate it. It's available in other places so you don't have to process the data just retrieve the count alone. It depends on what you're trying to do overall, but we don't know what that is at the moment.

[Tom]

It is just telling you what it did. 

With the NOPRINT option set it was smart enough to know it didn't need to keep running the select statement since it wasn't putting the result anywhere.  You can still use it to tell the difference between an empty query and one that returns at least on observation.

255   proc sql noprint;
256   select name,sex from sashelp.class where sex='M';
257   %put &=sqlobs  ;
SQLOBS=1
258   select name,sex from sashelp.class where sex='XXX';
NOTE: No rows were selected.
259   %put &=sqlobs  ;
SQLOBS=0

You will see similar results with SELECT ... INTO giving a different count depending whether you use the SEPARATED BY clause or not.

260   select name into :namelist from sashelp.class;
261   %put &=sqlobs &=namelist ;
SQLOBS=1 NAMELIST=Alfred
262   select name into :namelist separated by ' '
263     from sashelp.class where sex='F';
264   %put &=sqlobs &=namelist ;
SQLOBS=9 NAMELIST=Alice Barbara Carol Jane Janet Joyce Judy Louise Mary
265   quit;

[jffeudo86]

So in the case where I need to know the count of the variables from a dataset,
1.) do a proc contents (as in my example), and
2.) do a select count(*) into <variable> from the output of proc contents, rather than use the sqlobs variable.

[Reeza]

Pull that information from the sashelp.vtable information instead, it has the metadata for your SAS data sets, but not datasets that may reside on a server such as Oracle.

[Ksharp]

using the following code to replace.

PROC SQL ;

create table _null_ as 

SELECT *
FROM CarVars;
QUIT;