topic Re: Case Statement in SAS Programming

Case Statement

anonymous_user — Tue, 06 Jan 2015 17:19:17 GMT

Hi,

I have the oode below but the output isn't working as expected.

As you can see im looking to group the arrears_multiplier but byt having this case statement, all the accounts that are >=1 arrears fall into the 1+ including possession, how can I get the code to split it out like the following -

Arrears	Risk
1+ inc possession	£ 829,675,536.56
3+ inc possession	£ 327,507,427.14
6+ inc possession	£ 143,861,119.30

Code -

proc sql;

create table work.summary1 as

select

month_date,

accnum,

balance_outstanding,

arrears_multiplier,

accnum,

case

when arrears_multiplier >=1 or arrears_lit_stage_code in ('J8','L3') then '1+ including possession'

when arrears_multiplier >=3 or arrears_lit_stage_code in ('J8','L3') then '3+ including possession'

when arrears_multiplier >=6 or arrears_lit_stage_code in ('J8','L3') then '6+ including possession'

else 'null' end as Arrears,

sum(case when arrears_multiplier >= 1 or arrears_lit_stage_code in ('J8','L3') then balance_outstanding else 0 end) as e1_1plus,

sum(case when arrears_multiplier >= 3 or arrears_lit_stage_code in ('J8','L3') then balance_outstanding else 0 end) as e2_3plus,

sum(case when arrears_multiplier >= 6 or arrears_lit_stage_code in ('J8','L3') then balance_outstanding else 0 end) as e3_6plus,

sum(case when arrears_lit_stage_code in ('J8','L3') then balance_outstanding else 0 end) as e4_possession,

sum(case when Current_default = 'Y' then balance_outstanding else 0 end) as e5_default

from nov14

where

arrears_lit_stage_code not in ('L4','L5','L6')

group by month_date;

quit;

Re: Case Statement

PGStats — Tue, 06 Jan 2015 17:35:38 GMT

The case statement stops execution as soon as as it finds a match. Since something that is >= 3 is also >= 1, the second condition is always met by the first. Reverse the order of eveluation in the case statement if you want to avoid this.

Re: Case Statement

stat_sas — Tue, 06 Jan 2015 17:36:03 GMT

This is because arrears_multiplier >=1 includes 3 or greater also.

when arrears_multiplier >=1 or arrears_lit_stage_code in ('J8','L3') then '1+ including possession'

when arrears_multiplier >=3 or arrears_lit_stage_code in ('J8','L3') then '3+ including possession'

when arrears_multiplier >=6 or arrears_lit_stage_code in ('J8','L3') then '6+ including possession'

Re: Case Statement

Reeza — Tue, 06 Jan 2015 17:38:28 GMT

Should your OR be AND in your WHEN statements?

Anything that meets the second or third condition would meet the first condition and be already classified, so re-ordering them might help.

when arrears_multiplier >=1 or arrears_lit_stage_code in ('J8','L3') then '1+ including possession'

when arrears_multiplier >=3 or arrears_lit_stage_code in ('J8','L3') then '3+ including possession'

when arrears_multiplier >=6 or arrears_lit_stage_code in ('J8','L3') then '6+ including possession'

You're calculating a lot more variables in your code than shown in your desired output so hard to say what you want. Perhaps sample data and output would be helpful