rowsort with many variables

[George Kariuki]

I have 100 variables with data that I want to rank by row from the largest to smallest. Does it mean that in the

varlist

within

rowsort [varlist], gen(newvarlist)

I will have to type out all the hundred variables and the associated new ones? Is there a simpler way to say use a range where variables are not sequentially labelled or even how to use the (*) to add a number for new variables?

[Clyde Schechter]

The dash notation works in this context.

sysuse auto, clear
rowsort headroom-gear_ratio, gen(v1-v7)

It doesn't matter that the original variables are not sequentially labeled or otherwise amenable to wildcard abbreviation.

[Nick Cox]

rowsort is from the Stata Journal, as you are asked to explain (FAQ Advice #12).

You don't have to type out 100 new names.

Clyde Schechter gave an excellent answer. Here is another method, which can be varied.

forval j = 1/100 {
      local new `new' sorted`j'
}

rowsort ..., gen(`new')

The generate() option wants to see a list of new variable names. A wildcard doesn't qualify because Stata can't know how many names are implied by a wildcard when it parses what you type.

(You don't say what these data are; this problem could signal the need for a different data layout.)

[George Kariuki]

Thanks Clyde and Nick. The data is from a psychometric test that returns a host of career areas in which a subject can thrive. Please see a MWE here.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(overall_score customerexperiencemanager accountexecutive teaching stem engineer financialanalyst consultant)
99  88 77  99 68 81 61 86
97  88 69  94 39 70 62 81
91  73 84  99 81 60 62 71
85  98 87  93 48 22 42 63
83  94 51 100 74 56 66 61
80  61 57  93 62 62 69 74
78  33 89  87 88 19 43 34
72  60 76  91 76 17 55 58
68  84 47  95 45 24 38 47
61  95 57  94 25 21 24 24
61  55 39  77 21 35 46 32
60  88 55  89 52 18 13 48
58  45 23  83 28 49 22 33
46  69 75  47 40 41 70 51
44  27 37  59 51 49 35  8
39  93 69  75 13 22 24 24
33  90 38  80 64 27 14 29
30  44 48  30 44 15  9 47
24  96 86  89 22  0 47 38
23  90 89  83  5  2 23 14
23  57 39  81 23 14 37 21
20  60 45  97 63 26  9 53
19  95 26  81 12  7  9 25
15  45 17  66 28 16 37 11
10 100 38  58  9  1  5  4
 7  34  6  12 12 30  8  3
 6  39 53  42 51  9 10  6
 3  39 19   6  9  2  0 11
 3  18  6  10 24 28 36  8
 .   2  0  20 29  4  .  6
 .   9 28   8 11 21  .  6
 .   .  .   .  0  0  .  .
 .   .  .   .  .  .  .  .
 .   .  .   . 44 29  .  .
 .  66 53  68 43  . 76 57
end

------
There are 143 of such career fields and so I would want to rank for each subject, their highest to the lowest. In the end, I want to pick 15 top career matches. Does the above code maintain the same variable order for the generated variables? Or how will I know that sorted1 is similar to the first variable in the original order?

[Nick Cox]

Have you tried it? I wonder if you need rowranks (also Stata Journal) instead.

[George Kariuki]

I have tried the loop and it works by generating a set of new variables with new variable names "sorted1....sorted143". The difficulty is telling what sorted1 is in the original variable. i.e. what career is sorted1 representing? I am yet to try rowranks.

[Nick Cox]

So, how and where would you expect that information to be stored? And what happens with ties? Consider people like

  +--------------------------------+
  |                      obs:   10 |
  |--------------------------------|
  | customerexperiencemanager   95 |
  |          accountexecutive   57 |
  |                  teaching   94 |
  |                      stem   25 |
  |                  engineer   21 |
  |          financialanalyst   24 |
  |                consultant   24 |
  +--------------------------------+

who sorts 21 24 24 ....

[George Kariuki]

As long as the generated variables have an indication of what they represent I am ok with them within the same dataset. The ties are ok as long as they follow each other.

[Nick Cox]

I don't think you can do that except something like this.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(overall_score customerexperiencemanager accountexecutive teaching stem engineer financialanalyst consultant)
99  88 77  99 68 81 61 86
97  88 69  94 39 70 62 81
91  73 84  99 81 60 62 71
85  98 87  93 48 22 42 63
83  94 51 100 74 56 66 61
80  61 57  93 62 62 69 74
78  33 89  87 88 19 43 34
72  60 76  91 76 17 55 58
68  84 47  95 45 24 38 47
61  95 57  94 25 21 24 24
end 

gen id = _n
rename (customer-consultant) score= 
reshape long score, i(id) j(which) string 
bysort id (score) : gen rank = _n 
reshape wide score which, i(id) j(rank)

list which1 score1 which2 score2 which3 score3 which4 score4  which5 score5 which6 score6 which7 score7 

     +----------------------------------------------------------------+
  1. |           which1 | score1 |                    which2 | score2 |
     | financialanalyst |     61 |                      stem |     68 |
     |------------------+--------+---------------------------+--------|
     |           which3 | score3 |                    which4 | score4 |
     | accountexecutive |     77 |                  engineer |     81 |
     |------------------+--------+---------------------------+--------|
     |           which5 | score5 |                    which6 | score6 |
     |       consultant |     86 | customerexperiencemanager |     88 |
     |----------------------------------------------------------------|
     |                           which7        |        score7        |
     |                         teaching        |            99        |
     +----------------------------------------------------------------+

     +----------------------------------------------------------------+
  2. |           which1 | score1 |                    which2 | score2 |
     |             stem |     39 |          financialanalyst |     62 |
     |------------------+--------+---------------------------+--------|
     |           which3 | score3 |                    which4 | score4 |
     | accountexecutive |     69 |                  engineer |     70 |
     |------------------+--------+---------------------------+--------|
     |           which5 | score5 |                    which6 | score6 |
     |       consultant |     81 | customerexperiencemanager |     88 |
     |----------------------------------------------------------------|
     |                           which7        |        score7        |
     |                         teaching        |            94        |
     +----------------------------------------------------------------+

     +----------------------------------------------------------------+
  3. |           which1 | score1 |                    which2 | score2 |
     |         engineer |     60 |          financialanalyst |     62 |
     |------------------+--------+---------------------------+--------|
     |           which3 | score3 |                    which4 | score4 |
     |       consultant |     71 | customerexperiencemanager |     73 |
     |------------------+--------+---------------------------+--------|
     |           which5 | score5 |                    which6 | score6 |
     |             stem |     81 |          accountexecutive |     84 |
     |----------------------------------------------------------------|
     |                           which7        |        score7        |
     |                         teaching        |            99        |
     +----------------------------------------------------------------+

     +----------------------------------------------------------------+
  4. |           which1 | score1 |                    which2 | score2 |
     |         engineer |     22 |          financialanalyst |     42 |
     |------------------+--------+---------------------------+--------|
     |           which3 | score3 |                    which4 | score4 |
     |             stem |     48 |                consultant |     63 |
     |------------------+--------+---------------------------+--------|
     |           which5 | score5 |                    which6 | score6 |
     | accountexecutive |     87 |                  teaching |     93 |
     |----------------------------------------------------------------|
     |                           which7        |        score7        |
     |        customerexperiencemanager        |            98        |
     +----------------------------------------------------------------+

     +----------------------------------------------------------------+
  5. |           which1 | score1 |                    which2 | score2 |
     | accountexecutive |     51 |                  engineer |     56 |
     |------------------+--------+---------------------------+--------|
     |           which3 | score3 |                    which4 | score4 |
     |       consultant |     61 |          financialanalyst |     66 |
     |------------------+--------+---------------------------+--------|
     |           which5 | score5 |                    which6 | score6 |
     |             stem |     74 | customerexperiencemanager |     94 |
     |----------------------------------------------------------------|
     |                           which7        |        score7        |
     |                         teaching        |           100        |
     +----------------------------------------------------------------+

     +----------------------------------------------------------------+
  6. |           which1 | score1 |                    which2 | score2 |
     | accountexecutive |     57 | customerexperiencemanager |     61 |
     |------------------+--------+---------------------------+--------|
     |           which3 | score3 |                    which4 | score4 |
     |         engineer |     62 |                      stem |     62 |
     |------------------+--------+---------------------------+--------|
     |           which5 | score5 |                    which6 | score6 |
     | financialanalyst |     69 |                consultant |     74 |
     |----------------------------------------------------------------|
     |                           which7        |        score7        |
     |                         teaching        |            93        |
     +----------------------------------------------------------------+

     +----------------------------------------------------------------+
  7. |           which1 | score1 |                    which2 | score2 |
     |         engineer |     19 | customerexperiencemanager |     33 |
     |------------------+--------+---------------------------+--------|
     |           which3 | score3 |                    which4 | score4 |
     |       consultant |     34 |          financialanalyst |     43 |
     |------------------+--------+---------------------------+--------|
     |           which5 | score5 |                    which6 | score6 |
     |         teaching |     87 |                      stem |     88 |
     |----------------------------------------------------------------|
     |                           which7        |        score7        |
     |                 accountexecutive        |            89        |
     +----------------------------------------------------------------+

     +----------------------------------------------------------------+
  8. |           which1 | score1 |                    which2 | score2 |
     |         engineer |     17 |          financialanalyst |     55 |
     |------------------+--------+---------------------------+--------|
     |           which3 | score3 |                    which4 | score4 |
     |       consultant |     58 | customerexperiencemanager |     60 |
     |------------------+--------+---------------------------+--------|
     |           which5 | score5 |                    which6 | score6 |
     | accountexecutive |     76 |                      stem |     76 |
     |----------------------------------------------------------------|
     |                           which7        |        score7        |
     |                         teaching        |            91        |
     +----------------------------------------------------------------+

     +----------------------------------------------------------------+
  9. |           which1 | score1 |                    which2 | score2 |
     |         engineer |     24 |          financialanalyst |     38 |
     |------------------+--------+---------------------------+--------|
     |           which3 | score3 |                    which4 | score4 |
     |             stem |     45 |                consultant |     47 |
     |------------------+--------+---------------------------+--------|
     |           which5 | score5 |                    which6 | score6 |
     | accountexecutive |     47 | customerexperiencemanager |     84 |
     |----------------------------------------------------------------|
     |                           which7        |        score7        |
     |                         teaching        |            95        |
     +----------------------------------------------------------------+

     +----------------------------------------------------------------+
 10. |           which1 | score1 |                    which2 | score2 |
     |         engineer |     21 |          financialanalyst |     24 |
     |------------------+--------+---------------------------+--------|
     |           which3 | score3 |                    which4 | score4 |
     |       consultant |     24 |                      stem |     25 |
     |------------------+--------+---------------------------+--------|
     |           which5 | score5 |                    which6 | score6 |
     | accountexecutive |     57 |                  teaching |     94 |
     |----------------------------------------------------------------|
     |                           which7        |        score7        |
     |        customerexperiencemanager        |            95        |
     +----------------------------------------------------------------+

Reversing the rank order is easy enough, but watch out for where missings go.