topic Re: how to create a new variable by codifying an existing variable in New SAS User

how to create a new variable by codifying an existing variable

ari2495 — Tue, 08 Nov 2022 15:44:55 GMT

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

thanks for help

Re: how to create a new variable by codifying an existing variable

PaigeMiller — Tue, 08 Nov 2022 16:06:32 GMT

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;

Re: how to create a new variable by codifying an existing variable

ari2495 — Tue, 08 Nov 2022 15:52:04 GMT

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?

Re: how to create a new variable by codifying an existing variable

PaigeMiller — Tue, 08 Nov 2022 15:53:28 GMT

It appears that you do want rating_num to be numeric.

Re: how to create a new variable by codifying an existing variable

ari2495 — Tue, 08 Nov 2022 15:54:48 GMT

yes exactly

Re: how to create a new variable by codifying an existing variable

ballardw — Tue, 08 Nov 2022 16:37:12 GMT

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;