Hi,

I have to merge the five columns (poor to excellent) as five categories of one variable health status e.g. 1.poor 2.fair 3.good 4.very good 5.excellent:


Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str20 location float(poor fair good verygood excellent)
"United States"        4.7 14.1 32.3 31.5 17.4
"Alabama"              6.5 16.2 32.4 29.5 15.4
"Alaska"               3.9 11.6   32   34 18.4
"Arizona"                5   14 33.3 30.1 17.6
"Arkansas"             7.7 17.5 33.5 28.1 13.2
"California"             4 14.3 33.5 29.9 18.4
"Colorado"             2.9 11.2 30.2 36.1 19.6
"Connecticut"          3.5 11.4 29.1 36.5 19.5
"Delaware"             3.7 15.1   32 31.6 17.6
"District of Columbia" 3.1 10.5 24.3 36.8 25.2
"Florida"              5.6 14.1 31.5 28.3 20.4
"Georgia"              4.8 15.2   32 28.7 19.2
"Hawaii"               3.4 12.7   36 30.4 17.5
"Idaho"                4.8 11.2 31.5 34.1 18.4
"Illinois"             3.9 13.7 33.3 32.5 16.5
"Indiana"              5.5 14.4 32.8 32.3   15
"Iowa"                 3.1 11.2 33.6 36.4 15.6
"Kansas"               4.2 13.4 32.8 33.9 15.8
"Kentucky"               7 16.4 33.2 30.9 12.5
"Louisiana"            6.9 15.8 32.8 28.4 16.1
"Maine"                5.3 13.2 30.4 35.1   16
"Maryland"             3.3 11.5 32.7 34.7 17.9
"Massachusetts"        3.4 10.7 29.8 35.3 20.8
"Michigan"             4.7 14.1 33.4 32.9 14.9
"Minnesota"            3.1 10.7 31.1 37.4 17.7
"Mississippi"          7.1 16.5 35.3 28.4 12.7
"Missouri"             5.4 13.5   32 33.5 15.7
"Montana"              4.5 10.7 30.6 36.6 17.5
"Nebraska"             3.3 11.3 33.6 34.7 17.2
"Nevada"               5.3 15.7 32.8 28.6 17.6
"New Hampshire"        4.1 10.5 29.9 36.8 18.7
"New Jersey"             .    .    .    .    .
"New Mexico"             5 16.1 33.9 28.3 16.7
"New York"             4.4 12.9   31 32.1 19.6
"North Carolina"       5.4   14 32.9 29.9 17.8
"North Dakota"         3.3 10.6 32.8 36.6 16.7
"Ohio"                   5 14.3 32.8 32.4 15.5
"Oklahoma"               6 15.9 33.8   30 14.3
"Oregon"               4.5 13.7 30.9 33.6 17.3
"Pennsylvania"         4.5 14.4 32.4 33.6 15.2
"Rhode Island"         4.3 12.8 32.3   33 17.7
"South Carolina"       5.2 13.7 32.8 32.7 15.7
"South Dakota"         4.5 11.5 32.1   33 18.9
"Tennessee"            6.4 15.3   31 33.4   14
"Texas"                4.8 16.4 32.9 28.7 17.2
"Utah"                 3.1 11.3 31.1 34.9 19.6
"Vermont"              3.4 10.3 30.6 38.4 17.3
"Virginia"             4.3 12.7 30.6 34.3 18.2
"Washington"           3.9 12.4   32 34.2 17.5
"West Virginia"        8.6   18   35   28 10.4
"Wisconsin"            4.1 11.6   34 35.1 15.3
"Wyoming"              4.5 12.2 31.2 33.5 18.7
"Guam"                 4.4 16.1 39.9   24 15.6
"Puerto Rico"          4.4   30   30 15.1 20.6
end
I tried the reshape and egen (rowmax) options, but none of them seem to be the right approach. Please let me know if this is doable.

Thank you.