BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ari2495
Obsidian | Level 7

hi, i am new to sas. I have a variable named RATING and I have to substitute it by creating a new variable called rating_num

this is the code i used: 

data pd_finali_rating;
set pd_finali;
keep RATING cluster_td anno_riferimento;
if RATING in ("ND", "NULL", "") then Rating_num=/10/.;
    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;

it gave me this error 

ari2495_0-1667922138904.png

thanks for help

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
Rating_num=/10/.;

 

is not valid SAS code. If you are assigning a value to a numeric variable named rating_num, you can't have slashes like that. On the other hand, you could assign the value of numeric 0 and then using a custom format, make it appear to be /10/. or whatever else you want.

 

So assign the value of 0 to the first case

 

if RATING in ("ND", "NULL", "") then Rating_num=0;

 

then

 

proc format;
    value code 0='/10/.';
run;

proc datasets library=work nolist;
     modify pd_finali_rating;
     format rating_num code.;
run;

 

--
Paige Miller

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26
Rating_num=/10/.;

 

is not valid SAS code. If you are assigning a value to a numeric variable named rating_num, you can't have slashes like that. On the other hand, you could assign the value of numeric 0 and then using a custom format, make it appear to be /10/. or whatever else you want.

 

So assign the value of 0 to the first case

 

if RATING in ("ND", "NULL", "") then Rating_num=0;

 

then

 

proc format;
    value code 0='/10/.';
run;

proc datasets library=work nolist;
     modify pd_finali_rating;
     format rating_num code.;
run;

 

--
Paige Miller
ari2495
Obsidian | Level 7

no it is a charachter variable.

let me explain 

i have 

RATING

AAA and i have to turn it to 1 and so on. how would you fix it so?

PaigeMiller
Diamond | Level 26

It appears that you do want rating_num to be numeric.

--
Paige Miller
ballardw
Super User

You have an inconsistency. 'C' is assigned 21 and 22.

 

How sure are you that there will never be a value like "AAa" or "AaA" or other changes in case.

 

I would typically create a custom Informat that the Input function uses to turn the text into numeric values.

Example below. Rating is the name of the new Informat so it is easy to remember what it is used with. The UPCASE option turns all letters into upper case versions so you do not need to have separate listing for AAA aaa aAa Aaa AAa aAA aaA and such. This does assign the 'C' to only 21. If that is not correct then adjust the definition. Note that Proc Format will tell you about overlapping or duplicate assignments, which your If/Then/Else code will not.
This assigns missing to the values for "null". The Other=_error_ will generate an invalid data message in the log in case one of your Rating values is an error or just "new" such as ZZZ (not suggesting this is the case) but it will place a note in the log that you have an unexpected value and that allows you to address in a manner that seems fit. It may be adding the value to the missing such as someone using NA, not applicable, instead of ND.

Note that a custom informat can assign special missing, such as possibly .N for the Null or ND values. They would still be missing for calculations but you would be able to know why the value is missing, i.e. was created from those Rating values.

 

proc format;
invalue rating (upcase)
"AAA"=1
"AA+","AA1"=2
"AA","AA2"=3
"AA-","AA3"=4
"A+","A1"=5
"A","A2"=6
"A-","A3"=7
"BBB+","BAA1"=8
"BBB","BAA2"=9
"BBB-","BAA3"=10
"BB+","BA1"=11
"BB","BA2"=12
"BB-","BA3"=13
"B+","B1"=14
"B","B2"=15
"B-","B3"=16
"CCC+","CAA1"=17
"CCC","CAA2"=18
"CCC-","CAA3"=19
"CC","CA"=20
"C"=21
"SD","DDD"=22
"D","DD"=23
"ND", "NULL", ""=.
other=_error_;
;

data pd_finali_rating;
   set pd_finali;
   keep RATING cluster_td anno_riferimento;
   rating_num = input(rating,rating.);
run;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 661 views
  • 2 likes
  • 3 in conversation