BookmarkSubscribeRSS Feed

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 -

ArrearsRisk
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;

3 REPLIES 3
PGStats
Opal | Level 21

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

PG
stat_sas
Ammonite | Level 13

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'

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 383 views
  • 0 likes
  • 4 in conversation