Data Output

Reply
anonymous_user
Posts: 0

Data Output


Hi,

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 BTLSelf CertNon conf
Average LTV
<=50%
<=75%
<=100%
>100%
Account Balance
% Accounts with >=2.5% Arrears

proc sql;
create table work.asset as
select
sum(balance_outstanding) as balance,
mean (index_ltv)as average_ltv,
arrears_mulitplier,
case
when repprod in ('NCFast','NCNFas') then 'Non Conforming'
when repprod = 'CPrime' then 'Prime'
when repprod = 'CSCert' then 'Self Cert'
else 'BTL' end as asset,

case
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

from
gbasel.baseldec12
group by repprod, index_ltv;
quit;

Super User
Posts: 5,388

Re: Data Output

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"?

Data never sleeps
anonymous_user
Posts: 0

Re: Data Output

I just use base sas.

Im realyl unsure on what im needing to do to seperate the process. The rule for arrears is arrears_muliplier>=2.5 then its a % of the account balance.

Super User
Posts: 5,388

Re: Data Output

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.

Data never sleeps
anonymous_user
Posts: 0

Re: Data Output

I have done majoruty of this in a pivot.

How do I do a case statement to obtain the outputs on the first row - Average LTV split by repprod?

Super User
Posts: 5,388

Re: Data Output

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?

Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 190 views
  • 0 likes
  • 2 in conversation