Case Statement

Reply
Frequent Contributor
Posts: 138

Case Statement

Hi

I need help please with a case statement to work out the average LTV by product

proc sql;

create table work.avg_ltv as

select

case

when repprod in ('NCFast','NCNFas'), mean(index_ltv) then 'Non Conforming'

when repprod = 'CPrime', mean(index_ltv) then 'Prime'

when repprod = 'CSCert', mean(index_ltv) then 'Self Cert'

else 'btl' end as asset

from

gbasel.baseljun14;

quit;

Esteemed Advisor
Posts: 5,202

Re: Case Statement

Can you explain the logic?

It seems not logical to use a mean() calculation in an IN clause. That requires an exact match.

Data never sleeps
Frequent Contributor
Posts: 138

Re: Case Statement

I need to know the average LTV of accounts that fall into Prime BTL Self Cert or Non Confirming.

So if x amounts of accounts are 'Prime' what is the average LTV etc.

Esteemed Advisor
Posts: 5,202

Re: Case Statement

Sounds like Asset is your classification variable.

Mean() for each group can be calculated in the report.

If you create the report in SQL, just add mean(index_ltv) as mean_index_ltv as an output column. This together with a group by on Asset.

Data never sleeps
Grand Advisor
Posts: 17,464

Re: Case Statement

when repprod = 'CPrime', mean(index_ltv) then 'Prime'


The second part (mean(index_ltv)) does not make sense in your when statement.


proc sql;

create table work.avg_ltv as

select

mean(index_ltv) as avg_value,

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

from

gbasel.baseljun14

group by calculated asset;

quit;

Frequent Contributor
Posts: 138

Re: Case Statement

Thanks, how do I get this as a percentage?

Esteemed Advisor
Posts: 5,202

Re: Case Statement

Get what as a percentage?

Data never sleeps
Ask a Question
Discussion stats
  • 6 replies
  • 210 views
  • 0 likes
  • 3 in conversation