How to fix the rating?

[ari2495]

Hi, i'm trying to convert the rating to numbers but then when I plot the freq I see that all the ratin are 10. How to fix this?

proc import 
datafile = "C:\Users\amarroccel001\Desktop\DB\20221013_DB_.xlsx"
dbms=xlsx 
out=pd_finali;
sheet="sheet1";
run;
%let rating_unrated = 10;

data pd_finali_rating;
set pd_finali;
if RATING in ("ND", "NULL", "", "Unrated") then Rating_num=&rating_unrated.;
    else if RATING in ("AAA" "Aaa") then Rating_num=1;
    else if RATING in ("AA+" "Aa1") then Rating_num=2;
    else if RATING in ("AA" "Aa2") then Rating_num=3;
    else if RATING in ("AA-" "Aa3") then Rating_num=4;
    else if RATING in ("A+" "A1") then Rating_num=5;
    else if RATING in ("A" "A2") then Rating_num=6;
    else if RATING in ("A-" "A3") then Rating_num=7;
    else if RATING in ("BBB+" "Baa1") then Rating_num=8;
    else if RATING in ("BBB" "Baa2") then Rating_num=9;
    else if RATING in ("BBB-" "Baa3") then Rating_num=10;
    else if RATING in ("BB+" "Ba1") then Rating_num=11;
    else if RATING in ("BB" "Ba2") then Rating_num=12;
    else if RATING in ("BB-" "Ba3") then Rating_num=13;
    else if RATING in ("B+" "B1") then Rating_num=14;
    else if RATING in ("B" "B2") then Rating_num=15;
    else if RATING in ("B-" "B3") then Rating_num=16;
    else if RATING in ("CCC+" "Caa1") then Rating_num=17;
    else if RATING in ("CCC" "Caa2") then Rating_num=18;
    else if RATING in ("CCC-" "Caa3") then Rating_num=19;
    else if RATING in ("CC" "Ca") then Rating_num=20;
    else if RATING in ("C") then Rating_num=21;
    else if RATING in ("SD" "C" "DDD") then Rating_num=22;
    else if RATING in ("D" "DD") then Rating_num=23;
run;

[fja]

Hello,
does RATING contain the expected rating string? I.e. Have you output it via put or the like?
--fja

[ari2495]

hi I checked and there are many classes in the original dataset

[PaigeMiller]

Please show us a portion of this data set, as working SAS data step code, which you can type in yourself, or follow these instructions. Do not provide the data as an Excel file. Do not provide the data as a screen capture.

By the way, you should probably be showing us a portion of the data (following the above guidelines) every single time you present a problem here in the SAS communities.

[Kurt_Bremser · Accepted solution]

---

@ari2495 wrote:
hi I checked and there are many classes in the original dataset

---

Then none of those are caught by your code. This example code demonstrates that your data step works, given expected data:

data pd_finali;
infile datalines truncover;
input rating $;
datalines;
ND
NULL

Unrated
AAA
BBB-
CCC+
;

%let rating_unrated = 10;

data pd_finali_rating;
set pd_finali;
if RATING in ("ND", "NULL", "", "Unrated") then Rating_num=&rating_unrated.;
    else if RATING in ("AAA" "Aaa") then Rating_num=1;
    else if RATING in ("AA+" "Aa1") then Rating_num=2;
    else if RATING in ("AA" "Aa2") then Rating_num=3;
    else if RATING in ("AA-" "Aa3") then Rating_num=4;
    else if RATING in ("A+" "A1") then Rating_num=5;
    else if RATING in ("A" "A2") then Rating_num=6;
    else if RATING in ("A-" "A3") then Rating_num=7;
    else if RATING in ("BBB+" "Baa1") then Rating_num=8;
    else if RATING in ("BBB" "Baa2") then Rating_num=9;
    else if RATING in ("BBB-" "Baa3") then Rating_num=10;
    else if RATING in ("BB+" "Ba1") then Rating_num=11;
    else if RATING in ("BB" "Ba2") then Rating_num=12;
    else if RATING in ("BB-" "Ba3") then Rating_num=13;
    else if RATING in ("B+" "B1") then Rating_num=14;
    else if RATING in ("B" "B2") then Rating_num=15;
    else if RATING in ("B-" "B3") then Rating_num=16;
    else if RATING in ("CCC+" "Caa1") then Rating_num=17;
    else if RATING in ("CCC" "Caa2") then Rating_num=18;
    else if RATING in ("CCC-" "Caa3") then Rating_num=19;
    else if RATING in ("CC" "Ca") then Rating_num=20;
    else if RATING in ("C") then Rating_num=21;
    else if RATING in ("SD" "C" "DDD") then Rating_num=22;
    else if RATING in ("D" "DD") then Rating_num=23;
run;

proc print data=pd_finali_rating noobs;
run;

Result:

rating	Rating_num
ND	10
NULL	10
 	10
Unrated	10
AAA	1
BBB-	10
CCC+	17