CODEWITHSUNDEEP
stata
myth007
myth007

Reputation: 23

Obtain value corresponding to max value of another variable in Stata

Is it possible to obtain in Stata the observation value in one variable that corresponds to the max of another variable? (Something similar to this in SQL).

Edit: You are right, Nick. I was light on details. My dataset looks like this (wfs Google Docs table; couldn't figure out how to format a table here).

My aim is to create two variables with the "col" and "row" values corresponding to the max of "pressure" by side and condition.

Upvotes: 1

Views: 7852

Answers (1)

Nick Cox
Nick Cox

Reputation: 37338

That is rather general, but indeed yes is the short answer. Here are two specific examples, one for overall maximum, the other for maxima within groups. 

. sysuse auto, clear
(1978 Automobile Data)

. su mpg , meanonly

. list weight if mpg == r(max)

     +--------+
     | weight |
     |--------|
 71. |  2,040 |
     +--------+

. egen maxmpg = max(mpg), by(rep78)

. list rep78 maxmpg weight if mpg == maxmpg

    +-------------------------+
    | rep78   maxmpg   weight |
    |-------------------------|
 7. |     .       26    2,230 |
14. |     3       29    2,110 |
18. |     2       24    2,750 |
40. |     1       24    2,730 |
45. |     .       26    2,520 |
    |-------------------------|
52. |     2       24    2,690 |
63. |     4       30    1,980 |
71. |     5       41    2,040 |
    +-------------------------+

Some general notes:

  1. Watch out for ties, especially when data are (held as) integers.

  2. Watch out for missing values on either your selection variable or any other. (Missing values are sorted to the end of the dataset, for example.)

  3. Watch out for precision problems when comparing with maxima. (Not documented here in this answer, but search precision, faq leads to many discussions.)

http://www.stata-journal.com/article.html?article=dm0055 discusses related techniques.

(UPDATE)

Sounds like 

. bysort side condition (pressure) : gen rowmax = row[_N]

. bysort side condition (pressure) : gen colmax = col[_N]

For a tutorial on by: see http://www.stata-journal.com/sjpdf.html?articlenum=pr0004 That's a link to a free .pdf you should read. As before, if you have missing values on pressure, you will need something different, as the missings will be sorted to the end of each block of side and condition.

Upvotes: 2

Related Questions