Identifying variable with the maximum value in a row

[Hilary B]

Dear Statalist,

I have data on cropped area for 11 different crops (variables) in 500 districts of India (observations). I am trying to rank each crop according to its area within each district, in order to determine the most cultivated crop, second most cultivated crop, third most... etc. Using the code below, I generated a new variable that holds the highest value of all crops for each observation:

unab xvars: rice wheat crscereals maize pulses oilseeds sugarcane cotton fruitsveg rootstubers fodder
local xarg : subinstr local xvars " " ",", all
generate xmax = max(`xarg')

But I do not know for which crop this value is given. Is there a way to return the variable name rather than value??

Also, is there a way to identify the second highest value/variable? and third highest?

I was able to do something similar in excel and ArcGIS using a conditional statement, but the intuition is not clear here.

Thank you!!!

Hilary

[Nick Cox]

 
unab xvars: rice wheat crscereals maize pulses oilseeds sugarcane cotton fruitsveg rootstubers fodder

gen which_max = "" 
gen max = 0 

quietly foreach x of local xvars { 
    replace which_max = "`x'" if `x' > `max' 
    replace max = `x' if `x' > `max' 
}

See also http://www.stata-journal.com/sjpdf.h...iclenum=pr0046 for a review of working rowwise.

[Maarten Buis]

This is easier done in long format:

// create some example data
clear
set obs 500
gen district = _n
foreach var in rice wheat crscereals maize pulses oilseeds sugarcane cotton fruitsveg rootstubers fodder {
    gen prod`var' = rnormal()
}

// reshape to long format
reshape long prod@, i(district) j(product) string

// rank the products within districts
gen minusprod = -prod
bys district (minusprod): gen rank = _n
drop minusprod

// look at the most productive products
list district product prod if rank == 1

// look at the second most productive products
list district product prod if rank == 2

// etc.

[Hilary B]

Thank you, both! Maarten, your code worked perfectly. I greatly appreciate your speedy and smart responses.

[Moundir]

I present myself first: My name is Moundir from Algeria
I used your program "sequential logit", I have a question : Can we use a set of variables at each level of decision? If so, how?
Thank you

[Nick Cox]

Moundir: I'd start a new thread. The topic of sequential logit has nothing to do with this thread. Please read the FAQ advice before you do that.

[Aaditya Dar]

For anyone looking at this and getting a syntax error while using Nick's code, the following should work (max is a variable and not a local):

unab xvars: rice wheat crscereals maize pulses oilseeds sugarcane cotton fruitsveg rootstubers fodder

gen which_max = "" 
gen max = 0 

quietly foreach x of local xvars { 
    replace which_max = "`x'" if `x' > max
    replace max = `x' if `x' > max
}

[Nick Cox]

Thanks for the fix.

[Cynthia Inglesias]

Nick,

I would like to generate a new variable equal to 1 if `x' is equal to max and 0 otherwise. But when i add the following line into your loop nothing happens. Why?

 generate new`x' = 1 if `x' == max

[Nick Cox]

This thread is getting confusing. If I backtrack to my code in #2, add the typo fix in #7, add a reproducible sandbox, and add the code in #9 to the loop, then

clear 
set obs 10 
set seed 2803 
forval j = 1/5 { 
    gen x`j' = ceil(100 * runiform()) 
} 

gen which_max = "" 
gen max = 0 
unab xvars : x* 

quietly foreach x of local xvars { 
    replace which_max = "`x'" if `x' > max 
    replace max = `x' if `x' > max 
    generate new`x' = 1 if `x' == max
}

list 

     +----------------------------------------------------------------------------------+
     | x1   x2   x3    x4   x5   which_~x   max   newx1   newx2   newx3   newx4   newx5 |
     |----------------------------------------------------------------------------------|
  1. | 93   80   67    28    5         x1    93       1       .       .       .       . |
  2. | 34   64   86   100   19         x4   100       1       1       1       1       . |
  3. | 78   10   86    78   19         x3    86       1       .       1       .       . |
  4. | 11    4   18    11   74         x5    74       1       .       1       .       1 |
  5. | 34   10   46    47   20         x4    47       1       .       1       1       . |
     |----------------------------------------------------------------------------------|
  6. |  3   26   38    92   38         x4    92       1       1       1       1       . |
  7. | 18    8   34    97   25         x4    97       1       .       1       1       . |
  8. | 63   11   39    12   95         x5    95       1       .       .       .       1 |
  9. | 40   59   50    26   23         x2    59       1       1       .       .       . |
 10. | 39   24   16    67   23         x4    67       1       .       .       1       . |
     +----------------------------------------------------------------------------------+

I don't get that "nothing happens". However, the code is wrong: it's just is the current x variable equal to the maximum seen so far?

You need to take that extra line out of the loop and add another loop:

 
quietly foreach of local xvars { 
    gen new`x' = `x' == max 
}

Note: (0,.1) indicators are more useful than (1, .) indicators.

[Cynthia Inglesias]

Thanks for the reply Nick. The second loop should be foreach x of local xvars. But even after correcting for this i get an error: newx1 already defined ?

There should be only one "1" in each line (the maximum) and the rest zero.

[Nick Cox]

Thanks for the fix, but I draw your attention in turn to the line

You need to take that extra line out of the loop

Here's sample code in one:

clear 
set obs 10 
set seed 2803 
forval j = 1/5 { 
    gen x`j' = ceil(100 * runiform()) 
} 

gen which_max = "" 
gen max = 0 
unab xvars : x* 

quietly foreach x of local xvars { 
    replace which_max = "`x'" if `x' > max 
    replace max = `x' if `x' > max 
}
 
quietly foreach of local xvars { 
    gen new`x' = `x' == max 
}

Note also that the initialization max = 0 will need fixing if you have any negative values.

[Cynthia Inglesias]

Nick your code works now but when i apply it to my data i only get zeros. Any ideas why this is happening? Note that it identifies correctly the max and which_max, which is a crazy.

[Nick Cox]

Possibly a precision problem. Are all variables the same storage type? If some are float and some double, you need to watch out.

[Cynthia Inglesias]

Thanks Nick. Apparently the max variable must be generated as double as well. One last question: if values are negative how max should be initialized? Any ideas or guidelines?