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;
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.
PG
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'
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.