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
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.
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
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;
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
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;
"Don't know how to accept this as a solution."
Looks as though you've managed to solve this little problem ;). Thanks.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: