0 observations read from the data set

[ajacobs]

Is there a macrovariable where we can see of if the where clause results in

No observations were selected from data set STATDA.BD2RCFA.
NOTE: There were 0 observations read from the data set STATDA.BD2RCFA.
WHERE (pillar='P') and (vwk_dt=17743) and (pst_vznd_kd='GZKANT');
NOTE: The PROCEDURE PRINT used the following resources:

This for sending a message to the web page with no rows selected.

[sbb]

The technique to use is with a DATA step that precedes the PROC PRINT -- you would add the NOBS= to a SET statement, and code an IF/THEN DO;/END; *AHEAD OF THE SET STATEMENT* to check your SAS variable supplied on the NOBS= parameter for a zero value, and then use CALL SYMPUT to set the macro variable. And remember to end your DATA step with a RUN; so SAS will compile the code.

Important to understand here is that the macro variable is resolved by the SAS compiler, so your DATA step must complete, before you can test the SYMPUT macro variable.

If you want conditional execution of the PROC PRINT, you will need to code a SAS macro to test the SYMPUT variable.

Sample code below:

%MACRO X;
%* data step below purpose to generate test data. ;
DATA test_file;
AAA = 'BBB';
RUN;
%* presume we will have data to process. ;
%LET GOTDATA = 1;
DATA _NULL_;
IF OBSCOUNT = 0 THEN
CALL SYMPUT('GOTDATA','0');
SET test_file NOBS=OBSCOUNT;
WHERE AAA = 'AAA';
RUN;
%IF &GOTDATA %THEN %DO;
%* your conditional code goes here. ;
%END;
%MEND X;
%X;

Scott Barry
SBBWorks, Inc.

[ajacobs]

Thanks for your reply, but what I want to know is the availability of a macrovar. If such a var exist, then we don't need to read the data twice.
Maybe a suggestion for the SASware ballot.

[sbb]

A SAS PROC SQL step generates the SAS automatic variable SQLOBS based on your query results. The value will be from zero to a maximum value for the observation count selected. Suggest a DOC review of the SAS SQL procedure and SQLOBS.

Scott Barry
SBBWorks, Inc.

[deleted_user]

**I do not know if, internally, reporting procedures create a macro variable like the SQLOBS information in PROC SQL, but ....
~ ~ ~ ~ ~
a new feature of SAS release 9.2 may help.

Among the information extracted by PROC SCAPROC are pointers to any macro variable read or written by the step.

To discover if the information you seek is created, have a go with SCAPROC 😉

PeterC

[sbb]

Humbly, I believe your request is akin to a "cart before the horse condition" -- think about it, unless I am misunderstanding your original question/post:

Before executing any SAS DATA step, you want SAS to maintain a system macro variable with the number of observations found in the upcoming DATA step (or maybe a PROC step too!) -- to go as far as compiling your SAS code in case there is a WHERE condition to apply. Consider the SAS system overhead required for such a "feature".

Examples provided with the SQLOBS and CALL SYMPUT are easily coded and work quite effectively.

Scott Barry
SBBWorks, Inc.

[deleted_user]

sorry, Scott

I thought it was to be populated by the proc, so that action could be taken, when a where clause excluded all rows


PeterC

[deleted_user]

you would not need to read the data twice.

For base SAS data sets, the NUBS information is available from the header.

For other sources, you would only need to read the first available row.

Macros for that purpose has been written and published to SAS-L. For example, see the macro %HaveAny() among the discussions in the SAS-L posting at http://listserv.uga.edu/cgi-bin/wa?A2=ind0207D&L=sas-l&P=R36781

PeterC

[ajacobs]

Yes, I know the exitence of SQLOBS in PROC SQL. But what I suggest is that we need a automatic macrovar like SQLOBS that we could use in all the procedures when we use a WHERE clause.
In this case PROC PRINT with a WHERE statement.

Albert Jacobs

[ajacobs]

That 's the reason why I suggest for the creation of a new automatic macrovar with the number of observations read or write by a procedure with a where clause. Internally SAS knows this number because you could see it in the log. The availability of such a macrovar gives a lot of possibilities without preprocessing the data or the need for reading twice.

Albert

[sbb]

SAS does *NOT* know the observation count until your SAS program step is parsed, compiled and then executed.

My specific point is concerning the SAS system overhead involved with such a pre-processing automatic variable -- maybe that was unclear in my post reply.

Frankly, what you're suggesting is unique under a specific condition, and it can be addressed with a few SAS program lines, rather than suggesting the SAS system figure out what you really want to know before executing your next program step.

Good luck in your quest, my friend.

Scott Barry
SBBWorks, Inc.

[ajacobs]

Some more explanation:
We only use a procedure on an existing dataset. This dataset can contain 10000 observations, but with a where clause the result could be 0 observations.

Albert

[JackHamilton]

Yes, SAS knows the numbers of observations in a data set under certain conditions, but what you want is not the number of observations but the number of observations meeting a WHERE condition.

An alternative to writing a data step to calculate this number is the ATTRN function with the NLOBSF parameter, which you can use in a macro without causing a step boundary. It will, however, have to read every observation in the data set, so it's not faster.

If you only want to know "is there at least one observation fulfilling the where clause?", then the data step can be faster because it can stop after it finds the first match. Of course, if there are 100,000,000 observations and only the very last one fulfills the where clause, you won't have saved any processing.

[sbb]

Okay, I recant my somewhat myopic opinion on this post.

Yes, it would be very useful for SAS Institute to consider a BallotWare item adding a SAS AUTOMATIC variable representing the number of "selected observations" for the most recent SAS DATA or PROC step.

Too many times, I am forced to program my way through a the "no observations selected" condition by adding my own code (in a DATA step, whether I enjoy it or not) setting a macro variable (like SQLOBS) but for my other PROC and DATA step executions. The reason is that when SAS PROC PRINT does not select any observations, no output is sent to the ODS or SASLIST destination and the resulting output (document) is empty -- not very informative to end-users, by the way.

A justification for this automatic variable which would be available *AFTER* the previous PROC or DATA step (other than PROC SQL), would permit me to execute conditional macro code based on SYSOBS or whatever SAS wants to call the variable.

And maybe, just maybe, for some future present to the SAS user community, certail SAS macro language such as %IF / %END may be executed without having to wrap a %MACRO / %MEND around it, in order to conditionally execute a complete DATA step.


Scott Barry
SBBWorks, Inc.

[JackHamilton]

Such an automatic variable might be useful, but it needs further refinement. What happens when there's more than one input data set in a data step? What happens when there's a double-DOW loop that reads the same input data set twice? What happens when POINT= or INDEX= is used?

The ability to use %IF in open code would be really, really helpful. I think I saw an example, once of a case where allowing it would cause something bad to happen, but it was sufficiently obscure that I can't remember what it was.