,
Please review the following. I included some brief comments to hopefully explain along the way what is happening. This will process the multiple market_types together. Feel free to ask any followup questions you may have.
data one;
infile cards truncover;
length Market_type $ 9;
input id $ (Q_RATING R_RATING S_RATING) (:$7.);
do market_type='Retail', 'Wholesale', 'SME';
output;
end;
cards4;
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-
;;;;
data two;
infile cards truncover;
input Rank (Market_type Rating_LKUP Q_RATING R_RATING S_RATING R_PCT) (:$9.);
cards4;
24 Retail UR Unrated Unrated Unrated -%
23 Retail 1 Aaa AAA AAA 20%
22 Retail 2+ Aa1 AA+ AA+ 20%
21 Retail 2 Aa2 AA AA 20%
20 Retail 2- Aa3 AA- AA- 20%
19 Retail 3+ A1 A+ A+ 50%
18 Retail 3 A2 A A 50%
17 Retail 3- A3 A- A- 50%
16 Retail 4+ Baa1 BBB+ BBB+ 100%
15 Retail 4 Baa2 BBB BBB 100%
14 Retail 4- Baa3 BBB- BBB- 100%
13 Retail 5+ Ba1 BB+ BB+ 100%
12 Retail 5 Ba2 BB BB 100%
11 Retail 5- Ba3 BB- BB- 100%
10 Retail 6+ B1 B+ B+ 150%
9 Retail 6 B2 B B 150%
8 Retail 6- B3 B- B- 150%
7 Retail 7+ Caa1 CCC+ CCC+ 150%
6 Retail 7 Caa2 CCC CCC 150%
5 Retail 7- Caa3 CCC- CCC- 150%
4 Retail 8
3 Retail 9
2 Retail 10
24 SME UR Unrated Unrated Unrated -%
23 SME 1 Aaa AAA AAA 30%
22 SME 2+ Aa1 AA+ AA+ 30%
21 SME 2 Aa2 AA AA 30%
20 SME 2- Aa3 AA- AA- 30%
19 SME 3+ A1 A+ A+ 60%
18 SME 3 A2 A A 60%
17 SME 3- A3 A- A- 60%
16 SME 4+ Baa1 BBB+ BBB+ 110%
15 SME 4 Baa2 BBB BBB 110%
14 SME 4- Baa3 BBB- BBB- 110%
13 SME 5+ Ba1 BB+ BB+ 110%
12 SME 5 Ba2 BB BB 110%
11 SME 5- Ba3 BB- BB- 110%
10 SME 6+ B1 B+ B+ 160%
9 SME 6 B2 B B 160%
8 SME 6- B3 B- B- 160%
7 SME 7+ Caa1 CCC+ CCC+ 160%
6 SME 7 Caa2 CCC CCC 160%
5 SME 7- Caa3 CCC- CCC- 160%
4 SME 8
3 SME 9
2 SME 10
24 Wholesale UR Unrated Unrated Unrated -%
23 Wholesale 1 Aaa AAA AAA 10%
22 Wholesale 2+ Aa1 AA+ AA+ 10%
21 Wholesale 2 Aa2 AA AA 10%
20 Wholesale 2- Aa3 AA- AA- 10%
19 Wholesale 3+ A1 A+ A+ 40%
18 Wholesale 3 A2 A A 40%
17 Wholesale 3- A3 A- A- 40%
16 Wholesale 4+ Baa1 BBB+ BBB+ 90%
15 Wholesale 4 Baa2 BBB BBB 90%
14 Wholesale 4- Baa3 BBB- BBB- 90%
13 Wholesale 5+ Ba1 BB+ BB+ 90%
12 Wholesale 5 Ba2 BB BB 90%
11 Wholesale 5- Ba3 BB- BB- 90%
10 Wholesale 6+ B1 B+ B+ 140%
9 Wholesale 6 B2 B B 140%
8 Wholesale 6- B3 B- B- 140%
7 Wholesale 7+ Caa1 CCC+ CCC+ 140%
6 Wholesale 7 Caa2 CCC CCC 140%
5 Wholesale 7- Caa3 CCC- CCC- 140%
4 Wholesale 8
3 Wholesale 9
2 Wholesale 10
;;;;
data flip_two;
set two;
by market_type descending rank;
array r[3] Q_RATING R_RATING S_RATING;
drop i;
do i=1 to dim(r);
rating_type = vname(r);
rating = r;
output;
end;
drop Rating_LKUP Q_RATING R_RATING S_RATING;
run;
data want;
*initialize variables for hash, does not actually set;
if 0 then set one flip_two;
*define our hash lookup;
*parameters :: market_type, rating_type and rating;
*return :: rank, r_pct;
declare hash rlkup(dataset:'flip_two');
rlkup.definekey('market_type', 'rating_type', 'rating');
rlkup.definedata('rank', 'r_pct');
rlkup.definedone();
*define hash rates, used for keeping rank and r_pct;
*use ordered ascending to help find middle/min later;
*use multidata to avoid collision on equal ranks for different rating_types;
declare hash rates(ordered:'a', multidata:'y');
rates.definekey('rank');
rates.definedata('r_pct');
rates.definedone();
*use the iterator to move through rates array and find min/middle;
declare hiter iter;
*create loop to process data from ds :: one;
do until(done);
set one end=done;
*this array contains each rating to loop over and lookup rank and r_pct from hash;
array h[3] Q_RATING R_RATING S_RATING;
drop i;
do i=1 to dim(h);
*we manually define keys to get rating_type and rating from h array;
rc = rlkup.find(key: market_type, key: vname(h), key: h);
*add the rank and r_pct to rates hash;
rc = rates.add();
end;
*the number of missing ratings from input dataset :: one;
miss = cmiss(of h
); *if all missing then get Unrated r_pct, you may need to make changes here besed on your criteria;
if miss=3 then rc = rlkup.find(key:market_type, key:'Q_RATING', key:'Unrated');
else do;
*create new iterator object to move through rates hash;
iter = _new_ hiter('rates');
*move through rates hash and find min/middle rank;
*min is first because rates hash sorted 'd';
iter.first();
*middle if miss=0 get next after first of iter;
if miss=0 then iter.next();
*to clear the rates hash we need to delete the iter;
iter.delete();
end;
output;
*clear the hash for next row;
rates.clear();
end;
keep id market_type q_rating r_rating s_rating rank r_pct;
stop;
run;
. Hence the reason, I reached out for help.
