01-28-2015 05:12 AM
I have a table below in which I need to populate. I have some code but im missing some key things off to complete but not sure what I need to do to the code to populate correctlyI need to the percentage of the accoutn balance. My code is below the table. Any help woud be appreciated.
|Prime||BTL||Self Cert||Non conf|
|% Accounts with >=2.5% Arrears|
create table work.asset as
sum(balance_outstanding) as balance,
mean (index_ltv)as average_ltv,
when repprod in ('NCFast','NCNFas') then 'Non Conforming'
when repprod = 'CPrime' then 'Prime'
when repprod = 'CSCert' then 'Self Cert'
else 'BTL' end as asset,
when index_ltv <= 0.50 then '<=50%'
when index_ltv <= 0.75 then '<=75%'
when index_ltv <= 1.00 then '<=100%'
else '> 100%' end as ltv
group by repprod, index_ltv;
01-28-2015 05:19 AM
Doing calculations is one thing, creating a report another. You should probably separate those steps. Use PROC REPORT, PROC TABULATE, the data step (or a combination) for the report creation part. Perhaps as a Stored Process, called from Excel - what report tools do you have?
What is the calculation rule for "% Accounts with >=2.5% Arrears"?
01-28-2015 05:34 AM
That may be clear for you, not for me. Attach some sample data to visualize the calculation.
Use SQL to do your classifications/basic calculation on row level.
Use the other report techniques I mentioned to do dynamic aggregation and lay-out.
01-28-2015 06:50 AM
This is an aggregated value, and should probably be handled in report section of your program, not in this SQL.
Since you are mixing measures in the same report, chances are that you have to make two or three reports, and then try to put them together in the presentation layer, whatever that is.
is this a ad-hoc or a static/recurring report?