topic Re: How to fix the rating? in New SAS User

How to fix the rating?

ari2495 — Fri, 18 Nov 2022 11:32:26 GMT

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;

Re: How to fix the rating?

fja — Fri, 18 Nov 2022 11:18:56 GMT

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

Re: How to fix the rating?

ari2495 — Fri, 18 Nov 2022 11:20:37 GMT

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

Re: How to fix the rating?

PaigeMiller — Fri, 18 Nov 2022 11:57:08 GMT

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.

Re: How to fix the rating?

Kurt_Bremser — Fri, 18 Nov 2022 12:02:39 GMT

@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