Hi, Requesting your help for an efficient look up code either hashes, format or whatever for the following problem. I have two Data sets with the following variables and values: Dataset1: ID Rating1 Rating2 Rating3 900129 2000425 900135 Baa1 A- 2000328 900152 Baa1 NR A 900265 Ba1 BBB- 900005 Baa1 A+ 900108 Baa2 BBB+ A 900121 A1 A A+ 900008 A2 A+ 6964444 Baa3 BB+ 900149 BBB+ 900124 000100-502 900153 A2 A+ 2000315 900136 Baa1 BBB+ 900122 Aa3 AA- AA- 900155 A3 BBB+ BBB+ 1103127 900185 A1 A+ 900151 Baa1 900172 A2 A- A 900007 A- A 99900096 Aa3 A+ A+ 900216 A2 NR A+ 99900169 Baa1 BBB+ A+ 900016 A1 A- A+ 900385 Aa3 A+ AA- Dataset2: Rank Rating_LKUP Q-RATING R_RATING S_RATING R% 23 1 Aaa AAA AAA 20% 22 2+ Aa1 AA+ AA+ 20% 21 2 Aa2 AA AA 20% 20 2- Aa3 AA- AA- 20% 19 3+ A1 A+ A+ 50% 18 3 A2 A A 50% 17 3- A3 A- A- 50% 16 4+ Baa1 BBB+ BBB+ 100% 15 4 Baa2 BBB BBB 100% 14 4- Baa3 BBB- BBB- 100% 13 5+ Ba1 BB+ BB+ 100% 12 5 Ba2 BB BB 100% 11 5- Ba3 BB- BB- 100% 10 6+ B1 B+ B+ 150% 9 6 B2 B B 150% 8 6- B3 B- B- 150% 7 7+ Caa1 CCC+ CCC+ 150% 6 7 Caa2 CCC CCC 150% 5 7- Caa3 CCC- CCC- 150% 4 8 3 9 2 10 1 UR Unrated Unrated Unrated My objective is I need a solution to look up dataset1 to dataset 2 to populate the correct R% from dataset2 in dataset1 for each ID based on the following logic:- 1. For each ID there can either one or 3 ratings or none in dataset1. If there is one or more rating in dataset1 the corresponding look up columns in dataset2 are Q-Rating, R-rating and S-rating as you can see above. 2. For example in row3 of dataset, you would notice for ID:900135 there are 2 ratings Baa1 and A- in dataset1. The corresponding look up value i.e A- and Baa1 can be found in 7th and 8th row of dataset2 respectively. 3. So yeah, if there are 2 ratings in dataset1, there would be 2 records with corresponding look up values in dataset2 and likewise if 3 there are 3 ratings, there would be 3 records of corresponding look up values, and at max there can be only 3 in dataset2. 4. I need to pick the R% for each ID in dataset1 using the lowest rating or in other words with an example for ID:900135 i need to pick the R% from dataset2 using the obs that has the lowest rating, which in this case is 4+ rather than 3- considering the highest to lowest scale happens to be from 1 to 10 and so the R% would be 100% comparing 50% and 100%. 5. Therefore for the example ID:900135, I want the final result(WANT) as - ID Rating1 Rating2 Rating3 R% 900135 Baa1 A- 100 /*this has to be derived with 4+ being lower than 3- in look up rating when comparing Baa1 and A- , if that makes sense*/ I'd appreciate any help I can receive and that would really mean a lot. Many Thanks, Charlotte
... View more
