Help using Base SAS procedures

PROC SQL AND BLANK RECORDS

Reply
N/A
Posts: 1

PROC SQL AND BLANK RECORDS

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.
Super User
Posts: 5,435

Re: PROC SQL AND BLANK RECORDS

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
Data never sleeps
Super User
Posts: 5,435

Re: PROC SQL AND BLANK RECORDS

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
Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 120 views
  • 0 likes
  • 2 in conversation