How to assign rank according to the corresponding values

[Anna Aniston]

Dear All

I wanted to know how we can assign ranks a multiple choice questions based on the values of the categories.

My question 81 is asking the enumerators to arrange and number in a ascending order your expenditure heads, Starting with the lowest to the highest and after ranking they were asked to fill in the amount. But the rank and the amount does not match in many. For example in the first row Q81_3 is ranked 1 (lowest) and Q81_8 is ranked the second lowest. But if you look at the amount exp_annual3 is 1000 whereas exp_annual8 is only 500. There is a limit to how many variables can be specifed with dataex so I took only 6 in my example which is why some ranks are saying more than 6. But I wanted to know for each row how can I rank the variable's Q81_1 to Q81_6 according to the values in exp_annual1- exp_annual6. SOme exp will be missing for some HH beacuse each Household willonly select the expenditure that they incur.

copy starting from the next line ------------- ---------

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(q81__1 q81__2 q81__3 q81__4 q81__5 q81__6) long(exp_annual1    exp_annual2    exp_annual3    exp_annual4    exp_annual5    exp_annual6)
7  5  1  6  0  0  7000 1500  1000  4500    .   .
1  0  0  3  0  0 15000    .     .  6500    .   .
5  2  0  4  0  1  5000  700     .  4700    .  42
8  7  0  9  3  2  2000  150     .  9000   50 125
5  4  0  0  0  0  6000 1500     .     .    .   .
6  5  0  4  0  0  5000  200     .  2000    .   .
2  1  3  0  0  0  1500  300  1000     .    .   .
6  0  3  5  0  1 10000    .  1000 10000    . 184
7  4  0  6  0  1  4000  400     .  3500    . 100
4  0  0  3  0  1 10000    .     .  9000    . 285
1  2  0  0  0  0  3500  720     .     .    .   .
2  0  3  0  0  0 10000    .  4200     .    .   .
5  4  0  0  3  2 10000  600     .     .   90  42
4  3  0  0  0  2  5000  585     .     .    . 100
1  2  3  4  5  6  7000  600   800  9000  200 200
3  5  0  4  1  0  4000  720     .  6500  200   .
5  0  4  0  0  2 10000    .  1000     .    . 100
3  2  0  0  0  0 10000  700     .     .    .   .
5  3  4  0  0  1 10000  650  1500     .    . 200
5  0  3  2  0  0 10000    .  5000  4000    .   .
5  3  0  4  0  2 12000  650     .  6500    . 150
6  3  0  0  0  1  6000 1200     .     .    . 600
7  3  6  8  0  1  5000  780  2500  8500    .  70
2  1  0  0  3  4  2000  200     .     .  100  50
3  0  1  0  0  0 20000    .   500     .    .   .
6  4  0  0  0  1  6000  780     .     .    . 500
5  0  0  4  0  2  6000    .     .  5000    . 200
3  0  2  0  0  0  8000    .  2000     .    .   .
6  3  0  7  0  1 20000 1560     . 20000    . 300
7  4  3  8  0  1  5000  780   500  7500    . 100
3  2  1  0  0  4  8000  800  2000     .    . 400
2  0  3  0  0  0 14000    . 10000     .    .   .
5  0  3  4  1  0  7000    .  5000  4500  150   .
2  1  7  0  0  0  2000  300  1500     .    .   .
5  0  3  0  0  1 10000    .  3000     .    . 200
6  3  2  4  0  0 15000 1000  1500  4800    .   .
8  0  2  0  0  0 10000    .  5500     .    .   .
5  0  4  3  0  2  6000    .  5000   600    . 100
6  3  0  2  0  0  7000  700     .  1900    .   .
4  0  0  3  0  1  8000    .     .  5000    . 150
7  3  2  6  0  1  9000  950   300  5000    . 100
3  2  0  0  0  0  5000  700     .     .    .   .
8  2  1  7  0  0  5000  780  1000  4000    .   .
5  2  4  3  0  0  3000  500  1000  1000    .   .
3  0  0  0  0  2 15000    .     .     .    . 200
1  2  0  3  0  4  5000  720     .  8500    . 150
4  3  2  1  0  0 11000 1000  3000  7000    .   .
1  0  2  3  0  0  6000    .  1500 12500    .   .
5  3  0  4  0  2 25000  650     .  6000    . 150
4  2  0  3  0  0  4700  800     .   850    .   .
5  3  4  0  1  0  4000  800  1500     .  130   .
6  3  0  5  0  1 10000  700     .  7500    . 100
5  3  4  0  0  1  5000  650  1000     .    . 100
5  4  0  0  2  1  1500  600     .     .  100  42
4  0  0  2  0  0  8000    .     .  2300    .   .
1  2  0  3  4  5 20000  600     . 10000  100 100
1  2  3  0  4  5  3000  300  1000     .  100 150
5  3  4  0  0  1  6500  700  6000     .    .  35
3  2  0  6  0  0 15000 1500     . 13000    .   .
6  3  2  4  0  1 12000  900   600  7600    . 100
4  3  2  0  0  0  6000 1400   500     .    .   .
8  7  6  0  1  0 10000  670  1000     .  500   .
5  0  0  4  0  0  7000    .     .  1780    .   .
1  2  3  0  0  0  3000  720  3000     .    .   .
15 14 13 12 10 11  5000  720  2000 11000  100 200
3  0  0  0  2  0  5000    .     .     . 1000   .
5  3  0  0  1  0  4000  800     .     .  150   .
7  1  0  6  0  0 10000  350     .  5000    .   .
6  5  4  3  0  0  6000 1500  7000  8000    .   .
3  2  1  0  4  0  8000  800  1500     .  100   .
5  3  0  2  0  0  4000 1000     .   800    .   .
4  0  0  3  0  0  5000    .     .  2000    .   .
4  0  3  0  0  0  7000    .  2000     .    .   .
3  2  0  0  0  0  8000  650     .     .    .   .
8  4  5  7  0  1  7000  780  2500  4000    . 100
5  0  3  4  1  0  6000    .  4000  5000  200   .
4  0  0  2  3  0 20000    .     . 11000  100   .
4  3  2  0  1  0  4000  150  4000     .  100   .
5  4  0  0  2  1  5000  750     .     .  100  42
6  0  0  5  1  2  6500    .     .  5000  150 200
6  0  5  4  0  0  6000    .   500   500    .   .
5  3  0  4  0  1  2000  700     .  8000    .  50
3  0  0  4  0  1 13000    .     . 20000    . 300
2  1  0  3  0  0  5000  650     .  7500    .   .
6  3  0  0  1  5 13000  600     .     .  150 150
5  3  0  4  0  1 25000  800     .  4000    .  60
2  7  5  4  0  0  8000  350   800   900    .   .
5  0  3  4  0  1 16000    .  3000 14500    . 200
2  1  3  4  5  0  5000  300  3000  6000  100   .
1  2  3  4  5  6 25000  900  1500 11500   50 240
4  2  0  1  0  0  3000  700     .  2000    .   .
1  2  0  3  4  0 10000  900     .  6500  200   .
9  6  7  8  5  1 10000  700  2000  8200  200 100
7  3  0  6  0  1  6000  250     .  2000    . 150
1  2  3  0  4  0 10000  300  6000     . 1000   .
4  0  0  0  0  2  5000    .     .     .    . 100
6  3  4  5  0  0  7000  450   800  2021    .   .
3  0  0  0  1  0  4500    .     .     .  150   .
4  0  0  2  0  0  8000    .     .  7000    .   .
6  3  5  0  0  1  7000  780  1500     .    . 250
end

Thank you in advance

[Clyde Schechter]

drop q81__*

gen long obs_no = _n
preserve
reshape long exp_annual, i(obs_no)
sort obs_no exp_annual

by obs_no (exp_annual), sort: gen rank = _n if !missing(exp_annual)
drop exp_annual
drop if missing(rank)
rename _j q81__
reshape wide q81__, i(obs_no) j(rank)
tempfile holding
save `holding'
restore

merge 1:1 obs_no using `holding'

[Anna Aniston]

Thank you but this
by obs_no (exp_annual), sort: gen rank = _n if !missing(exp_annual) says exp_annual ambiguous abbreviation. Can you tell me why

[Clyde Schechter]

I cannot reproduce your error:

. * Example generated by -dataex-. To install: ssc install dataex
. clear

. input byte(q81__1 q81__2 q81__3 q81__4 q81__5 q81__6) long(exp_annual1    exp_annual2    exp_annual3    exp_annual4    exp_annual5    ex
> p_annual6)

       q81__1    q81__2    q81__3    q81__4    q81__5    q81__6   exp_annual1   exp_annual2   exp_annual3   exp_annual4   exp_annual5   ex
> p_annual6
  1. 7  5  1  6  0  0  7000 1500  1000  4500    .   .
  2. 1  0  0  3  0  0 15000    .     .  6500    .   .
  3. 5  2  0  4  0  1  5000  700     .  4700    .  42
  4. 8  7  0  9  3  2  2000  150     .  9000   50 125
  5. 5  4  0  0  0  0  6000 1500     .     .    .   .
  6. 6  5  0  4  0  0  5000  200     .  2000    .   .
  7. 2  1  3  0  0  0  1500  300  1000     .    .   .
  8. 6  0  3  5  0  1 10000    .  1000 10000    . 184
  9. 7  4  0  6  0  1  4000  400     .  3500    . 100
 10. 4  0  0  3  0  1 10000    .     .  9000    . 285
 11. 1  2  0  0  0  0  3500  720     .     .    .   .
 12. 2  0  3  0  0  0 10000    .  4200     .    .   .
 13. 5  4  0  0  3  2 10000  600     .     .   90  42
 14. 4  3  0  0  0  2  5000  585     .     .    . 100
 15. 1  2  3  4  5  6  7000  600   800  9000  200 200
 16. 3  5  0  4  1  0  4000  720     .  6500  200   .
 17. 5  0  4  0  0  2 10000    .  1000     .    . 100
 18. 3  2  0  0  0  0 10000  700     .     .    .   .
 19. 5  3  4  0  0  1 10000  650  1500     .    . 200
 20. 5  0  3  2  0  0 10000    .  5000  4000    .   .
 21. 5  3  0  4  0  2 12000  650     .  6500    . 150
 22. 6  3  0  0  0  1  6000 1200     .     .    . 600
 23. 7  3  6  8  0  1  5000  780  2500  8500    .  70
 24. 2  1  0  0  3  4  2000  200     .     .  100  50
 25. 3  0  1  0  0  0 20000    .   500     .    .   .
 26. 6  4  0  0  0  1  6000  780     .     .    . 500
 27. 5  0  0  4  0  2  6000    .     .  5000    . 200
 28. 3  0  2  0  0  0  8000    .  2000     .    .   .
 29. 6  3  0  7  0  1 20000 1560     . 20000    . 300
 30. 7  4  3  8  0  1  5000  780   500  7500    . 100
 31. 3  2  1  0  0  4  8000  800  2000     .    . 400
 32. 2  0  3  0  0  0 14000    . 10000     .    .   .
 33. 5  0  3  4  1  0  7000    .  5000  4500  150   .
 34. 2  1  7  0  0  0  2000  300  1500     .    .   .
 35. 5  0  3  0  0  1 10000    .  3000     .    . 200
 36. 6  3  2  4  0  0 15000 1000  1500  4800    .   .
 37. 8  0  2  0  0  0 10000    .  5500     .    .   .
 38. 5  0  4  3  0  2  6000    .  5000   600    . 100
 39. 6  3  0  2  0  0  7000  700     .  1900    .   .
 40. 4  0  0  3  0  1  8000    .     .  5000    . 150
 41. 7  3  2  6  0  1  9000  950   300  5000    . 100
 42. 3  2  0  0  0  0  5000  700     .     .    .   .
 43. 8  2  1  7  0  0  5000  780  1000  4000    .   .
 44. 5  2  4  3  0  0  3000  500  1000  1000    .   .
 45. 3  0  0  0  0  2 15000    .     .     .    . 200
 46. 1  2  0  3  0  4  5000  720     .  8500    . 150
 47. 4  3  2  1  0  0 11000 1000  3000  7000    .   .
 48. 1  0  2  3  0  0  6000    .  1500 12500    .   .
 49. 5  3  0  4  0  2 25000  650     .  6000    . 150
 50. 4  2  0  3  0  0  4700  800     .   850    .   .
 51. 5  3  4  0  1  0  4000  800  1500     .  130   .
 52. 6  3  0  5  0  1 10000  700     .  7500    . 100
 53. 5  3  4  0  0  1  5000  650  1000     .    . 100
 54. 5  4  0  0  2  1  1500  600     .     .  100  42
 55. 4  0  0  2  0  0  8000    .     .  2300    .   .
 56. 1  2  0  3  4  5 20000  600     . 10000  100 100
 57. 1  2  3  0  4  5  3000  300  1000     .  100 150
 58. 5  3  4  0  0  1  6500  700  6000     .    .  35
 59. 3  2  0  6  0  0 15000 1500     . 13000    .   .
 60. 6  3  2  4  0  1 12000  900   600  7600    . 100
 61. 4  3  2  0  0  0  6000 1400   500     .    .   .
 62. 8  7  6  0  1  0 10000  670  1000     .  500   .
 63. 5  0  0  4  0  0  7000    .     .  1780    .   .
 64. 1  2  3  0  0  0  3000  720  3000     .    .   .
 65. 15 14 13 12 10 11  5000  720  2000 11000  100 200
 66. 3  0  0  0  2  0  5000    .     .     . 1000   .
 67. 5  3  0  0  1  0  4000  800     .     .  150   .
 68. 7  1  0  6  0  0 10000  350     .  5000    .   .
 69. 6  5  4  3  0  0  6000 1500  7000  8000    .   .
 70. 3  2  1  0  4  0  8000  800  1500     .  100   .
 71. 5  3  0  2  0  0  4000 1000     .   800    .   .
 72. 4  0  0  3  0  0  5000    .     .  2000    .   .
 73. 4  0  3  0  0  0  7000    .  2000     .    .   .
 74. 3  2  0  0  0  0  8000  650     .     .    .   .
 75. 8  4  5  7  0  1  7000  780  2500  4000    . 100
 76. 5  0  3  4  1  0  6000    .  4000  5000  200   .
 77. 4  0  0  2  3  0 20000    .     . 11000  100   .
 78. 4  3  2  0  1  0  4000  150  4000     .  100   .
 79. 5  4  0  0  2  1  5000  750     .     .  100  42
 80. 6  0  0  5  1  2  6500    .     .  5000  150 200
 81. 6  0  5  4  0  0  6000    .   500   500    .   .
 82. 5  3  0  4  0  1  2000  700     .  8000    .  50
 83. 3  0  0  4  0  1 13000    .     . 20000    . 300
 84. 2  1  0  3  0  0  5000  650     .  7500    .   .
 85. 6  3  0  0  1  5 13000  600     .     .  150 150
 86. 5  3  0  4  0  1 25000  800     .  4000    .  60
 87. 2  7  5  4  0  0  8000  350   800   900    .   .
 88. 5  0  3  4  0  1 16000    .  3000 14500    . 200
 89. 2  1  3  4  5  0  5000  300  3000  6000  100   .
 90. 1  2  3  4  5  6 25000  900  1500 11500   50 240
 91. 4  2  0  1  0  0  3000  700     .  2000    .   .
 92. 1  2  0  3  4  0 10000  900     .  6500  200   .
 93. 9  6  7  8  5  1 10000  700  2000  8200  200 100
 94. 7  3  0  6  0  1  6000  250     .  2000    . 150
 95. 1  2  3  0  4  0 10000  300  6000     . 1000   .
 96. 4  0  0  0  0  2  5000    .     .     .    . 100
 97. 6  3  4  5  0  0  7000  450   800  2021    .   .
 98. 3  0  0  0  1  0  4500    .     .     .  150   .
 99. 4  0  0  2  0  0  8000    .     .  7000    .   .
100. 6  3  5  0  0  1  7000  780  1500     .    . 250
101. end

.
. drop q81__*

.
. gen long obs_no = _n

. preserve

. reshape long exp_annual, i(obs_no)
(note: j = 1 2 3 4 5 6)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                      100   ->     600
Number of variables                   7   ->       3
j variable (6 values)                     ->   _j
xij variables:
exp_annual1 exp_annual2 ... exp_annual6   ->   exp_annual
-----------------------------------------------------------------------------

. sort obs_no exp_annual

.
. by obs_no (exp_annual), sort: gen rank = _n if !missing(exp_annual)
(249 missing values generated)

. drop exp_annual

. drop if missing(rank)
(249 observations deleted)

. rename _j q81__

. reshape wide q81__, i(obs_no) j(rank)
(note: j = 1 2 3 4 5 6)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                      351   ->     100
Number of variables                   3   ->       7
j variable (6 values)              rank   ->   (dropped)
xij variables:
                                  q81__   ->   q81__1 q81__2 ... q81__6
-----------------------------------------------------------------------------

. tempfile holding

. save `holding'
file C:\Users\clyde\AppData\Local\Temp\ST_4c4_000002.tmp saved

. restore

.
. merge 1:1 obs_no using `holding'

    Result                           # of obs.
    -----------------------------------------
    not matched                             0
    matched                               100  (_merge==3)
    -----------------------------------------

The only thing I can think of is if your full data set contains some other variables that begin with exp_annual but that you did not show in your example.

Please post back with a -dataex- example that reproduces this problem with this code and I will try to propose a workaround.