topic Re: Using weighted average to consolidate ratings in Transition Matrix in SAS Programming

Using weighted average to consolidate ratings in Transition Matrix

MACRO_LOOP — Thu, 26 Jul 2018 12:46:06 GMT

Hi Everyone,

I am struggling with a task in SAS. I am trying to consolidate a Matrix into distinct rows and columns using the weighted average of a variable to perform the consolidation.

The data that I am working with is shown below.

I need to collapse/consolidate the matrix so that each "Consolidated_Rating" row is distinct. I need to use the "nb" variable to create a weighted average to perform the consolidation.

For example, to create a distinct row for "AA", I will find the weighted average of the following rows ("AA1","AA2", & "AA3"). The weighted average should be created using the "nb" variable for the above letter ratings.

As I see it, there are two key parts to the task:

1. Perform this consolidation across rows (by using the weighted average where the "nb" variable is used as the weighting function) and across columns (This part is much simpler, I simply take the simple Average of the columns to perform the consolidation).

2. The additional complication I face is for the corner rating grade for each column. this requires a more complex calculation.

I have managed to create exactly what I want in Excel as a starting point. The attached excel workbook contains the exact calculation that I need to perform.

If there is additional information that I can add, please let me know.

All help is greatly appreciated.

DATA INPUT;
input Nb$ Rating$ CONSOLIDATED_RATING$ AAA AA1 AA2 AA3 A1 A2 A3 BBB1 BBB2 BBB3 BB1 BB2 BB3 B1 B2 B3 C1 C2 C3 CC D;
datalines;
2768 AAA AAA 87.4277 5.5275 2.9263 0.8309 0.1806 0.2529 0.1445 0 0 0 0 0.0723 0 0 0.0361 0 0 0.0723 0 0 0
1394 AA1 AA 2.5825 77.5466 11.1191 4.0172 0.7891 0.3587 0.2152 0.0717 0.1435 0.0717 0 0 0 0 0 0 0 0 0 0 0
4482 AA2 AA 0.4462 1.1825 81.3253 8.0321 2.5881 1.1379 0.4462 0.4016 0.1785 0.0669 0.0669 0.0446 0.0223 0.0223 0 0.0223 0 0.0446 0 0.0223 0.0223
4523 AA3 AA 0.0663 0.1548 3.8691 77.8023 10.3913 2.1888 0.6633 0.2874 0.199 0.1105 0.0442 0 0 0.0442 0.1327 0 0 0 0 0 0.0221
6498 A1 A 0 0.0462 0.5386 4.3706 78.3318 8.7412 2.5546 0.7695 0.3078 0.0769 0.0769 0.0923 0.0154 0.0923 0.0462 0 0 0 0 0 0.0462
9431 A2 A 0.053 0.053 0.2969 0.4559 4.9305 78.6555 6.9876 2.7038 1.0815 0.2863 0.159 0.159 0.0848 0.1166 0.0212 0 0 0.0106 0 0.0106 0.0636
8710 A3 A 0.023 0.0115 0.0689 0.1263 0.4707 6.1653 77.9334 7.853 2.2732 0.5052 0.1263 0.1378 0.1263 0.1493 0.023 0.0115 0 0.0115 0 0.023 0.0574
8550 BBB1 BBB 0 0.0117 0.0468 0.0234 0.2339 0.8655 7.3099 74.9825 8.7251 1.7895 0.386 0.3275 0.1637 0.2105 0.117 0.0234 0 0.0234 0 0.0117 0.1053
9924 BBB2 BBB 0.0101 0 0.0302 0.0101 0.1008 0.3829 1.1084 7.497 75.8061 6.6707 1.4208 0.6751 0.3023 0.262 0.1411 0.0403 0.0202 0.0202 0.0101 0.0202 0.1411
7359 BBB3 BBB 0.0136 0 0.0272 0.0544 0.0544 0.1495 0.3261 1.3453 9.3899 72.4283 5.7481 2.3101 1.0192 0.3805 0.299 0.1767 0.1767 0.0544 0.0136 0.0679 0.2446
4433 BB1 BB 0.0677 0 0 0 0 0.1128 0.0902 0.5414 2.0528 11.9558 64.2229 7.5795 3.316 1.1956 0.6993 0.2256 0.1353 0.1353 0.0902 0.0226 0.2707
5718 BB2 BB 0 0 0.0525 0 0 0.07 0.07 0.1924 0.5946 2.4484 9.7936 64.638 8.8492 2.6058 1.1542 0.4022 0.1749 0.1574 0.1049 0.07 0.6296
7363 BB3 BB 0 0 0 0.0136 0.0136 0.0136 0.0679 0.1358 0.2445 0.3939 1.8742 9.8329 62.8548 9.2489 3.3818 0.8692 0.2852 0.2716 0.0951 0.1087 1.1408
10333 B1 B 0 0.0097 0 0.0387 0 0.0387 0.0774 0.0581 0.0484 0.0968 0.2903 1.5484 7.8874 63.9892 9.2906 2.6323 0.9775 0.5323 0.1548 0.1645 2.2549
8562 B2 B 0 0 0.0117 0.0117 0 0.0467 0.0584 0.0117 0.0701 0.0234 0.1518 0.2569 1.3198 8.0589 62.1934 8.8881 2.4177 1.168 0.3621 0.327 4.1345
3831 B3 B 0 0 0 0 0.0261 0.0261 0 0.1044 0.0522 0.1566 0.1044 0.1827 0.5482 2.6364 10.1018 52.6494 7.7264 3.289 1.1224 0.8353 8.5095
1252 C1 C 0 0 0 0 0.0799 0 0 0 0.0799 0.1597 0 0.0799 0.4792 1.5176 3.6741 11.5016 34.984 6.4696 3.115 1.0383 23.3227
779 C2 C 0 0 0 0 0 0 0.1284 0.2567 0.1284 0 0 0.3851 0.1284 0.7702 2.8241 6.8036 5.6483 36.5854 2.3107 1.7972 27.9846
261 C3 C 0 0 0 0 0 0 0 0 0 0 0 0 1.1494 0.7663 1.1494 3.0651 4.2146 5.7471 27.9693 3.0651 39.8467
171 CC CC 0 0 0 0 0 0 1.1696 0 0 0 0 0.5848 0.5848 0.5848 1.7544 2.3392 1.7544 8.7719 0 11.6959 61.9883
0 D D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
;
run;

All help is very much appreciated

Re: Using weighted average to consolidate ratings in Transition Matrix

PeterClemmensen — Thu, 26 Jul 2018 14:41:55 GMT

Where in the Excel sheet is the final desired output?

When I read your request, I figure you want a 9x2 'matrix' as the final result? 9 rows for the 9 distinct variable values for CONSOLIDATED_RATING and two colums: CONSOLIDATED_RATING and a weighted average with the relevant Nb values as weights (where the CONSOLIDATED_RATING value prepends the Rating variable value).

I don't see that output anywhere. Please elaborate on how you want your final result to look like

Re: Using weighted average to consolidate ratings in Transition Matrix

MACRO_LOOP — Thu, 26 Jul 2018 15:16:10 GMT

Hi,

The excel file contains the exact data that I will be working with.

The top section (rows 1-22) contain the raw data and the bottom section (rows 46 onward) contains the output I need. The final output is an 18x18 matrix.

Thankfully, the only rating that I need to perform the consolidation on is to consolidate the "CCC" ratings to "C". The formulas used to do this are highlighted in yellow in the workbook.

I hope this makes it easier.

I appreciate you taking time to read my request!

Re: Using weighted average to consolidate ratings in Transition Matrix

MACRO_LOOP — Sat, 28 Jul 2018 05:30:17 GMT

I've been working away on trying to find a solution to this issue.

I have it working using a series of filters across multiple query wizards but it is not very efficient.

I think an ideal solution would involve an arrays and some type of loop.

Does anyone have suggestions on how I might achieve this using the below data?

Re: Using weighted average to consolidate ratings in Transition Matrix

PeterClemmensen — Sat, 28 Jul 2018 07:05:06 GMT

I am still not sure what you want the final output to look like. In your first request you want to collapse to distinct variable values for CONSOLIDATED_RATING (which has 9 distinct values).

Now you want an 18x18 matrix( i'm not sure where that is in your excel workbook or how to get there)

and you only want it for CCC to C...

Please be specific about what you want the final output to look like. Preferably not in an Excel workbook.

Re: Using weighted average to consolidate ratings in Transition Matrix

MACRO_LOOP — Sun, 29 Jul 2018 06:22:45 GMT

Hi,

My apologies. I can see that my request has been very confusing and inconsistent.

I have been working through the issue and the target output has changed since my initial request.

Shown below is the input data and target and target output data.

I want my output to be an 18x18 matrix. The only consolidation that I now require is to consolidate the following Letter Ratings (CCC1,CCC2, & CCC3) to C.

The code for the INPUT data and OUTPUT data that I am working with is shown below.

DATA INPUT_DATA;
input LETTER_RATING$ Number_RATINGS  AAA AA1 AA2 AA3 A1 A2 A3 BBB1 BBB2 BBB3 BB1 BB2 BB3 B1 B2 B3 CCC1 CCC2 CCC3 CC D
; 
datalines;

AAA 19013 0.838321 0.02735 0.013201 0.002893 0.000631 0.01115 0.000053 0 0.001788 0 0 0.000158 0.000105 0 0 0.000368 0 0 0 0 0
AA1 2910 0.058763 0.850515 0.012027 0.00378 0.003436 0.003436 0.000344 0 0 0 0 0 0.004811 0 0 0 0 0 0 0.000344 0
AA2 3281 0.008229 0.00762 0.853703 0.001524 0.00061 0.021945 0.001219 0.000305 0.001829 0.002743 0 0.001219 0 0 0.000305 0 0 0 0 0 0
AA3 816 0.001225 0.034314 0.017157 0.757353 0.014706 0 0.002451 0.002451 0.001225 0.003676 0 0 0 0 0 0 0 0 0 0 0
A1 1716 0.004079 0.002914 0.055361 0.002914 0.824592 0.002331 0.001166 0 0 0 0 0 0 0 0.000583 0 0 0 0 0 0
A2 3398 0.004709 0.006769 0.018246 0.008534 0.00206 0.820777 0.002354 0.009123 0.005592 0.00412 0.000294 0.004414 0.000294 0.000294 0.005592 0.001177 0.000294 0.002354 0.000294 0.001471 0
A3 108 0.046296 0.009259 0.009259 0 0.018519 0.018519 0.759259 0.009259 0.027778 0 0 0 0 0 0.018519 0.009259 0 0 0 0 0
BBB1 72 0 0.055556 0.013889 0 0.013889 0 0 0.513889 0.013889 0 0 0 0 0 0 0.305556 0 0 0 0 0
BBB2 256 0.003906 0.003906 0 0 0 0.003906 0.003906 0.003906 0.71875 0 0.007813 0.011719 0.054688 0.003906 0.046875 0.042969 0 0.003906 0 0.007813 0
BBB3 81 0 0 0.049383 0 0 0 0 0 0 0.679012 0 0 0.012346 0 0.012346 0.049383 0 0.012346 0.012346 0.024691 0
BB1 16 0 0 0 0.125 0 0 0 0 0 0 0.8125 0.0625 0 0 0 0 0 0 0 0 0
BB2 93 0 0 0 0 0 0.010753 0 0.010753 0.021505 0 0 0.795699 0.010753 0 0 0.032258 0 0.010753 0 0.032258 0
BB3 70 0 0 0 0 0 0 0 0.028571 0 0 0 0.028571 0.542857 0.014286 0 0.085714 0.142857 0 0.042857 0 0
B1 18 0 0 0 0 0 0 0 0 0.055556 0 0 0 0 0.833333 0 0 0 0 0 0 0
B2 58 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.362069 0.155172 0 0.034483 0.017241 0.103448 0
B3 171 0 0 0 0 0 0 0 0 0 0.005848 0 0 0 0.005848 0.005848 0.690058 0 0.169591 0 0.040936 0
CCC1 12 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.75 0 0.083333 0.166667 0
CCC2 130 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.007692 0 0.846154 0.007692 0.069231 0
CCC3 33 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.939394 0 0
CC 125 0 0 0.008 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.832 0
C 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
;

run ;




DATA REQUIRED_OUTPUT_DATA;
input LETTER_RATING$ AAA AA1 AA2 AA3 A1 A2 A3 BBB1 BBB2 BBB3 BB1 BB2 BB3 B1 B2 B3  C D
; 
datalines;

AAA 0.838321 0.02735 0.013201 0.002893 0.000631 0.01115 0.000053 0 0.001788 0 0 0.000158 0.000105 0 0 0.000368 0 0
AA1 0.058763 0.850515 0.012027 0.00378 0.003436 0.003436 0.000344 0 0 0 0 0 0.004811 0 0 0 0 0
AA2 0.008229 0.00762 0.853703 0.001524 0.00061 0.021945 0.001219 0.000305 0.001829 0.002743 0 0.001219 0 0 0.000305 0 0 0
AA3 0.001225 0.034314 0.017157 0.757353 0.014706 0 0.002451 0.002451 0.001225 0.003676 0 0 0 0 0 0 0 0
A1 0.004079 0.002914 0.055361 0.002914 0.824592 0.002331 0.001166 0 0 0 0 0 0 0 0.000583 0 0 0
A2 0.004709 0.006769 0.018246 0.008534 0.00206 0.820777 0.002354 0.009123 0.005592 0.00412 0.000294 0.004414 0.000294 0.000294 0.005592 0.001177 0.002942 0.000294
A3 0.046296 0.009259 0.009259 0 0.018519 0.018519 0.759259 0.009259 0.027778 0 0 0 0 0 0.018519 0.009259 0 0
BBB1 0 0.055556 0.013889 0 0.013889 0 0 0.513889 0.013889 0 0 0 0 0 0 0.305556 0 0
BBB2 0.003906 0.003906 0 0 0 0.003906 0.003906 0.003906 0.71875 0 0.007813 0.011719 0.054688 0.003906 0.046875 0.042969 0.003906 0.011719
BBB3 0 0 0.049383 0 0 0 0 0 0 0.679012 0 0 0.012346 0 0.012346 0.049383 0.024692 0.012346
BB1 0 0 0 0.125 0 0 0 0 0 0 0.8125 0.0625 0 0 0 0 0 0
BB2 0 0 0 0 0 0.010753 0 0.010753 0.021505 0 0 0.795699 0.010753 0 0 0.032258 0.010753 0
BB3 0 0 0 0 0 0 0 0.028571 0 0 0 0.028571 0.542857 0.014286 0 0.085714 0.185714 0
B1 0 0 0 0 0 0 0 0 0.055556 0 0 0 0 0.833333 0 0 0 0
B2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.362069 0.155172 0.051724 0.068966
B3 0 0 0 0 0 0 0 0 0 0.005848 0 0 0 0.005848 0.005848 0.690058 0.169591 0.011696
C 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.00571405714285714 0.868571302857143 0.628571542857143
D 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1


;
run;

Thank You for taking time to read this and all help is very much appreciated.

Re: Using weighted average to consolidate ratings in Transition Matrix

MACRO_LOOP — Wed, 01 Aug 2018 08:15:18 GMT

Hi Everyone,

I have been trying several different approaches to resolve the below issue. I have a series of Query builders set up that solve the problem but I fear that it will lead to issues in the future when I update the raw input data as there is a lot of hard-coding involved.

If anyone has suggestions on how I can approach the problem with a more elegant approach, I would be very grateful for any input.

Thank You for taking time to read this!

Re: Using weighted average to consolidate ratings in Transition Matrix

PeterClemmensen — Wed, 01 Aug 2018 11:42:13 GMT

If you specify exactly how the values 0.00571405714285714, 0.868571302857143 and 0.628571542857143 are calculated for the C row, I will write some code for you.

Re: Using weighted average to consolidate ratings in Transition Matrix

MACRO_LOOP — Wed, 01 Aug 2018 11:57:08 GMT

It is quite hard to explain how the calculation works in words. To avoid any confusion, I have recreated the calculation that I need to be performed in excel.

The exact calculation used to calculate the the values 0.00571405714285714, 0.868571302857143 and 0.628571542857143 are calculated for the C row are highlighted in pink in the attached excel file.

I hope the excel file makes things easier rather than complicating the issue further. If there is any further explanation that I can add, please let me know and I will do my best to explain.

Thank You for the reply. All help really is appreciated as I have been working on this for a long time now, without any luck.