finding second highest value

[Martina Vn]

Hello Statalist,

I would like to ask : I have a list of variables that sums up drinking in units ( 7 days - 7 lists ) and I would like to find a highest value for each participant ( the particular day when he drunk the most) , as well as a second highest value. Is there a command how I can do this please?

thank you very much for your help

[Rich Goldstein]

if you look at the help for egen, you will see a function called "rank" (there are different ways to define "rank" so read the help carefully; note also that this function allows the use of "by" so you can rank within each person

[Nick Cox]

Rich is naturally right about egen but the question to me implies that the 7 days for each person are held as 7 variables, not 7 observations.

If so, then see http://www.stata-journal.com/article.html?article=pr0046 and the associated rowranks and rowsort commands.

[Martina Vn]

so I have 7 days entry of alcohol consumption and I want to find out which are the two days with highest consumption for each participant, so I can do after an average of these two days.

The command below does not work:

rank(alc82_1u alc82_2u alc82_3u alc82_4u alc82_5u alc82_6u alc82_7u) [, field|track|unique]

thank you very much

[Rich Goldstein]

the command is "egen", not "rank"; as Nick says in #3, since you have a wide format, you need to use the rowranks command (use search to find and install); or, you can reshape to long and use egen with the rank function; if you reshape, then remember to use the "by" option to limit your results to ranking within person

[Nick Cox]

Here's an example. You need to install rowsort first.

. clear

. set seed 2803

. set obs 10
number of observations (_N) was 0, now 10

. forval j =1/7 {
  2.         gen alc82_`j'u = floor(10 * runiform()^2)
  3. }

. list

     +----------------------------------------------------------------------------+
     | alc82_1u   alc82_2u   alc82_3u   alc82_4u   alc82_5u   alc82_6u   alc82_7u |
     |----------------------------------------------------------------------------|
  1. |        8          6          4          0          0          0          2 |
  2. |        1          3          7          9          0          6          1 |
  3. |        5          0          7          6          0          0          8 |
  4. |        0          0          0          0          5          2          4 |
  5. |        1          0          2          2          0          3          3 |
     |----------------------------------------------------------------------------|
  6. |        0          0          1          8          1          7          2 |
  7. |        0          0          1          9          0          2          8 |
  8. |        3          0          1          0          8          0          0 |
  9. |        1          3          2          0          0          7          8 |
 10. |        1          0          0          4          0          4          1 |
     +----------------------------------------------------------------------------+

. rowsort alc*, gen(r1-r7) descend

. list r?

     +----------------------------------+
     | r1   r2   r3   r4   r5   r6   r7 |
     |----------------------------------|
  1. |  8    6    4    2    0    0    0 |
  2. |  9    7    6    3    1    1    0 |
  3. |  8    7    6    5    0    0    0 |
  4. |  5    4    2    0    0    0    0 |
  5. |  3    3    2    2    1    0    0 |
     |----------------------------------|
  6. |  8    7    2    1    1    0    0 |
  7. |  9    8    2    1    0    0    0 |
  8. |  8    3    1    0    0    0    0 |
  9. |  8    7    3    2    1    0    0 |
 10. |  4    4    1    1    0    0    0 |
     +----------------------------------+

Then naturally it is just

 
gen wanted = (r1 + r2)/2