Aggregate functions

[AJ_Brien]

Hello,

I'm trying to use a simple aggregate function, aim is to find all codes where count(acct)=0. I'm wondering what is the correct way to use this, I've tried a couple of variations- assigned count with a variable name, made the where clause as null etc.

proc sql;
create table abc12 as
select tcd, count(acct) From check
where count(acct) = " " group by tcd;
quit;

And the error I keep getting is:

ERROR: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Appreciate the help!

Sample Data - what I have :

tcd	 acct
980	 2243003
665	 345
105	 32245030
665	 58603230
105	 32249531
651	
221	 6032264901
221	 265379
720	
980	 75660

what I'm looking for:

tcd	 count(acct)
980	 2
665	 2
105	 2
651	 0
221	 2
720	 0

and then from this table I want to extract all tcd values that have count(acct)=0

[novinosrin]

Not sure if i comprehended your description accurately.  Are you after this?

proc sql;
create table abc12 as
select tcd, sum(acct=0) 
From check
 group by tcd;
quit;

@AJ_Brien   You could post a sample of what you "have" and what you "want" for the input sample to avoid assumptions for the community members 

[AJ_Brien]

I updated my request with the samples, thank you!

[AJ_Brien]

I did this to get the 2nd table, but I have a feeling it might not be pulling any tcds that have count(acct)=0. Is there a way to ensure those tcds are pulled in too?

proc sql;
create table abc12 as
(select tcd, count(acct) From check
group by tcd
);
quit;

[novinosrin]

Hello @AJ_Brien   Thank you for the samples making it clear

data check;
infile cards truncover;
input tcd	 acct;
cards;
980	 2243003
665	 345
105	 32245030
665	 58603230
105	 32249531
651	
221	 6032264901
221	 265379
720	
980	 75660
;

proc sql;
create table abc12 as
select tcd, count(acct) as count
From check
group by tcd
having count=0;
quit;

[AJ_Brien]

I tried this variation before, it gives this error:

ERROR: Invalid column name 'count'.

I thought maybe it's because it's some sort of keyword so I changed it to something else, that didn't help either.

[novinosrin]

in your code, you don't seem to assign count as a variable i.e 

proc sql;
create table abc12 as
(select tcd, count(acct) From check
group by tcd
);
quit;

select tcd, count(acct) as count /*this is missing for the having to filter*/From check

[AJ_Brien]

yeah so I just changed your previous code to:
proc sql;
create table abc12 as
select tcd, count(acct)
From check
group by tcd
having count(acct)=0;
quit;

and it gave no errors. Thank you!

[novinosrin]

Hello @AJ_Brien 

I am not sure why would you want to execute count function twice. 

Approach should ideally be 

1. Count once and filter

You could do just

proc sql;
create table abc12 as
select tcd, 0 as count
From check
group by tcd
having count(acct)=0;
quit;

[AJ_Brien]

I'm not sure, but the moment I assigned a name to the aggregate function, it throws that error.
But this latest approach from you also works. Don't know how to accept this as a solution.

[hashman]

@AJ_Brien :

"Don't know how to accept this as a solution." 

Looks as though you've managed to solve this little problem ;). Thanks. 

[hashman · Accepted solution]

@AJ_Brien :

Since it appears that your processing logic is "select each TCD value for which ACCT is missing in all rows and tag it with COUNT=0", I see no need to compute the count aggregates and store them in an interim table (explicit or implicit) when you can implement the logic directly and simply as (note that for the sake of generality, I've added extra rows for 720 and 651):

data check ;             
  input tcd acct ;       
cards;                   
980    2243003           
720          .           
665        345           
105   32245030           
665   58603230           
651          .           
105   32249531           
651          .           
221 6032264901           
221     265379           
720          .           
980      75660           
run ;                    
                         
proc sql ;               
  create table nocount as
  select unique tcd      
       , 0 as count      
  from   check           
  where acct is null     
  ;                      
quit ;                   

UNIQUE (same as DISTINCT) is added in case if a TCD with ACCT missing in all rows has more than one row (as in the intentionally altered input data sample above). If you want to keep all such rows (in this case, 2 for 720 and 651 apiece), omit UNIQUE.

Kind regards

Paul D.