numerical rankings to string (alphabets)

[River Huang]

Dear All, I (actually my friend) have about one thousand observations. A representative subsample is

* Example generated by -dataex-. To install: ssc install dataex
clear
input float x
 3
 6
 9
 7
 2
 2
10
 3
 8
 5
 3
 6
 1
 6
 6
10
 9
 6
 3
 8
 1
 8
 2
 .
 6
 9
 7
 3
 .
 9
 5
 9
 6
 9
 4
 7
 7
 .
 1
 4
 2
 .
 5
 2
 3
 1
 6
 1
 4
 9
end

I wish to replace the numerical ranking to string ranking according to the following transformation

* Example generated by -dataex-. To install: ssc install dataex
clear
input float x str5 w
10 "AAA" 
 9 "AA+" 
 8 "AA"  
 7 "AA-" 
 6 "A+"  
 5 "A"   
 4 "A-"  
 3 "BBB+"
 2 "BBB" 
 1 "BBB-"
end

Any suggestions is highly appreciated (w is the desired final result). Thanks.

[Roman Mostazir]

I will suggest a very fundamental way but am sure there are other elegant way:

tostring x, gen(w) //convert to string first

gen newstr =    cond(w == "10", "AAA", ///
                cond(w == "9", "AA+", ///
                cond(w == "8", "AA", ///  
                cond(w == "7", "AA-" , ///
                cond(w == "6", "A+" , ///
                cond(w == "5", "A"  , ///
                cond(w == "4", "A-"  , ///
                cond(w == "3", "BBB+", ///
                cond(w == "2", "BBB" , ///
                cond(w == "1", "BBB-", "."))))))))))
                
li x w newstr in 1/10, noobs clean
    
x    w   newstr 
     3    3       BBB+  
     6    6         A+  
     9    9        AA+  
     7    7        AA-  
     2    2        BBB  
     2    2        BBB  
    10   10        AAA  
     3    3       BBB+  
     8    8         AA  
     5    5          A

[Joseph Coveney]

You could also use merge.

.ÿversionÿ15.1

.ÿ
.ÿclearÿ*

.ÿ
.ÿquietlyÿinputÿbyteÿx

.ÿ
.ÿtempfileÿfriends_dataset

.ÿquietlyÿsaveÿ`friends_dataset'

.ÿ
.ÿdropÿ_all

.ÿ
.ÿinputÿbyteÿxÿstr4ÿw

ÿÿÿÿÿÿÿÿÿÿÿÿxÿÿÿÿÿÿÿÿÿÿw
ÿÿ1.ÿ10ÿ"AAA"ÿ
ÿÿ2.ÿÿ9ÿ"AA+"ÿ
ÿÿ3.ÿÿ8ÿ"AA"ÿÿ
ÿÿ4.ÿÿ7ÿ"AA-"ÿ
ÿÿ5.ÿÿ6ÿ"A+"ÿÿ
ÿÿ6.ÿÿ5ÿ"A"ÿÿÿ
ÿÿ7.ÿÿ4ÿ"A-"ÿÿ
ÿÿ8.ÿÿ3ÿ"BBB+"
ÿÿ9.ÿÿ2ÿ"BBB"ÿ
ÿ10.ÿÿ1ÿ"BBB-"
ÿ11.ÿend

.ÿ
.ÿ*
.ÿ*ÿBeginÿhere
.ÿ*
.ÿmergeÿ1:mÿxÿusingÿ`friends_dataset',ÿnogenerateÿnoreport

.ÿformatÿwÿ%-4s

.ÿlistÿinÿ1/5,ÿnoobs

ÿÿ+----------+
ÿÿ|ÿxÿÿÿwÿÿÿÿ|
ÿÿ|----------|
ÿÿ|ÿ1ÿÿÿBBB-ÿ|
ÿÿ|ÿ2ÿÿÿBBBÿÿ|
ÿÿ|ÿ3ÿÿÿBBB+ÿ|
ÿÿ|ÿ4ÿÿÿA-ÿÿÿ|
ÿÿ|ÿ5ÿÿÿAÿÿÿÿ|
ÿÿ+----------+

.ÿ
.ÿexit

endÿofÿdo-file


.

Or label define and label values.

[River Huang]

Dear Roman, Thanks a lot for this helpful suggestion.

[Nick Cox]

Note that in #2 there is no need to convert to string. You could use conditions such as x == 1, etc.

Here is another way to do it, following Joseph Coveney in #3:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float x
 3
 6
 9
 7
 2
 2
10
 3
 8
 5
 3
 6
 1
 6
 6
10
 9
 6
 3
 8
 1
 8
 2
 .
 6
 9
 7
 3
 .
 9
 5
 9
 6
 9
 4
 7
 7
 .
 1
 4
 2
 .
 5
 2
 3
 1
 6
 1
 4
 9
end

local grades BBB- BBB BBB+ A- A A+ AA- AA AA+ AAA 

tokenize `grades' 

forval j = 1/10 { 
    label define wanted `j' "``j''" , modify 
} 

clonevar wanted = x 
label value wanted wanted 

* -groups- is from the Stata Journal 
groups x wanted, sep(0) 

  +-------------------------------+
  |  x   wanted   Freq.   Percent |
  |-------------------------------|
  |  1     BBB-       5     10.87 |
  |  2      BBB       5     10.87 |
  |  3     BBB+       6     13.04 |
  |  4       A-       3      6.52 |
  |  5        A       3      6.52 |
  |  6       A+       8     17.39 |
  |  7      AA-       4      8.70 |
  |  8       AA       3      6.52 |
  |  9      AA+       7     15.22 |
  | 10      AAA       2      4.35 |
  +-------------------------------+

[River Huang]

Dear Nick, Thanks for the wonderful solution. It is exactly what I `wanted' in mind.

[Nick Cox]

tokenize BBB- BBB BBB+ A- A A+ AA- AA AA+ AAA

will work fine too. The local isn't necessary, although it may be useful for other purposes.

[River Huang]

Got it, and thanks.

[River Huang]

Dear Nick, May I ask a related question? Suppose that I have data as

* Example generated by -dataex-. To install: ssc install dataex
clear
input float x str5 w
10 "AAA" 
 9 "AA+" 
 8 "AA"  
 7 "AA-" 
 6 "A+"  
 5 "A"   
 4 "A-"  
 3 "BBB+"
 2 "BBB" 
 1 "BBB-"
end

For each string in `w', there is a corresponding value in `x'. I'k like to have a variable, say z (generated from `w'), similar to the `wanted' variable in #5. In particular, i`z' will be blue in color, string in face value but numerical in principle (similar to result from encode). Any suggestion is highly appreciated. Thanks.

[River Huang]

I found a way to obtain the result.

label define rank 1 "BBB-" 2 "BBB" 3 "BBB+" 4 "A-" 5 "A" 6 "A+" 7 "AA-" 8 "AA" 9 "AA+" 10 "AAA"
encode w, gen(rank)

[Nick Cox]

Here is another way to do it:

 
local grades BBB- BBB BBB+ A- A A+ AA- AA AA+ AAA

forval j = 1/10 {
    gettoken this grades : grades
    label define wanted `j' "`this'" , modify
}

clonevar wanted = x
label value wanted wanted 

[River Huang]

Hi, Nick : Thanks again for this helpful suggestion.

[Romalpa Akzo]

1. The first issue (in #1) could be solved with a 1-line coding.

gen w = word("BBB- BBB BBB+ A- A A+ AA- AA AA+ AAA",x)

2. For the second issue (in #9), -labmask-, a wonderful package by Nick Cox, provides a convenient solution.

labmask x, value(w)

[River Huang]

Dear Romalpa, Thank you so much for these wonderful solutions.