02-14-2014 03:18 PM
create table FSDILX2 as
select comm_cd,count(ln_no) as ln,sum(balance) as bal
group by comm_cd
having count(ln_no) and
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?
02-14-2014 04:25 PM
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 count probably don't even need a variable: count(*) will give a count of records with in group of comm_cd.
02-15-2014 10:35 AM
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;
02-16-2014 01:05 PM
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)