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?
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.
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;
Maybe I'm missing something obvious here, but I would just get rid of the having clause, and add distinct in the count...?
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)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.