topic Re: Multiple Having clauses in sas in SAS Procedures

Multiple Having clauses in sas

Q1983 — Fri, 14 Feb 2014 20:18:14 GMT

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?

Re: Multiple Having clauses in sas

ballardw — Fri, 14 Feb 2014 21:25:21 GMT

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.

Re: Multiple Having clauses in sas

overmar — Sat, 15 Feb 2014 15:35:35 GMT

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;

Re: Multiple Having clauses in sas

LinusH — Sun, 16 Feb 2014 12:08:24 GMT

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

Re: Multiple Having clauses in sas

Tom — Sun, 16 Feb 2014 18:05:56 GMT

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)