topic Re: PROC SQL AND BLANK RECORDS in SAS Procedures

PROC SQL AND BLANK RECORDS

yunnan — Wed, 15 Oct 2008 08:58:20 GMT

I use SAS SQL to test for exceptions to rules that produce a count. I do this by building a string variable that contains text describing the exception with another variable that contains the exception count.
This is all done within the one SQL statement:
eg:
SELECT 'How many widgits' as DESCRIPTION,
COUNT (*) AS COUNTX
from z

DESCRIPTION COUNTX
How many widgits 10

The issue I have is when the count is zero the SQL produces no values or zero observations.
I'd like to know how to keep the column description but have the count set to zero.

Re: PROC SQL AND BLANK RECORDS

LinusH — Wed, 15 Oct 2008 10:30:06 GMT

Try to do your count as a sub-query in the from clause:

proc sql;
select 'How many widgets' as description, countx
from (select count (*) as countx from z);
quit;

/Linus

Re: PROC SQL AND BLANK RECORDS

LinusH — Wed, 15 Oct 2008 10:56:59 GMT

On the other hand, if your only wish is to see how many records there are in certain table, reading the table metadata is more efficient than doing a table scan by using count(*):

%let dsid=%sysfunc(open(Z));
%put How many widgets: %sysfunc(attrn(&dsid,NOBS));

/Linus