Help using Base SAS procedures

Multiple Having clauses in sas

Reply
Frequent Contributor
Posts: 131

Multiple Having clauses in sas

proc sql;

create table FSDILX2 as

select comm_cd,count(ln_no) as ln,sum(balance) as bal

from FSDILX1

group by comm_cd

having count(ln_no) and

sum(bal);

quit;

Obviously this is wrong.  I am trying to get output in a summary form something like this:

FSDILX (which is a comm_cd)  52 (which is the total loan numbers or ln_no)  $52,000 (which is the total balance or bal)

Would I need two separate proc statements for the count and the sum?  Can I combine into one statement?

Super User
Posts: 10,500

Re: Multiple Having clauses in sas

Having clauses are usually used to restrict the output to some range. I suspect you don't need one from the description of your output.

If I wanted to restrict the output to a minimum number of ln_no I would add:

Having count(ln_no) > 15

or a maximum total balance with:

Having sum(balance) <10000

and to apply both restricts use AND

Having count(*) > 15 and sum(balance)<10000

for example.

For count probably don't even need a variable: count(*) will give a count of records with in group of comm_cd.

Frequent Contributor
Posts: 83

Re: Multiple Having clauses in sas

Or if the goal was to actually only output the line for FSDILX 52 $52,000 then you could specify them in the having statement, otherwise the way the code is currently written I agree with ballardw, proc sql will get mad because you are asking about having something, but not specifying what you want it to have. For reference if you wanted the line you had specified earlier you could use this as the having statement

having comm_cd = "FSDILX" and count(ln_no) = 52 and sum(balance) = 52000;

Super User
Posts: 5,257

Re: Multiple Having clauses in sas

Maybe I'm missing something obvious here, but I would just get rid of the having clause, and add distinct in the count...?

Data never sleeps
Super User
Super User
Posts: 6,500

Re: Multiple Having clauses in sas

You need to reference the source variable BALANCE and not the derived variable BAL in the HAVING clause.

Also it does depend on the way that SAS evaluates numbers in boolean expressions.

I think you want:

having count(ln_no)>0 and not (sum(balance)=0)


Ask a Question
Discussion stats
  • 4 replies
  • 707 views
  • 0 likes
  • 5 in conversation