rowmax with variables names

[River Huang]

Dear All, Suppose that I have the following data

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(onehf twohf threehf)
1 2 3
4 6 5
9 7 8
end

I want to generate a new variable `m', which has the maximum values across all rows but replaced with the corresponding variable name as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(onehf twohf threehf) str10 m
1 2 3 "threehf"
4 6 5 "twohf"
9 7 8 "onehf"
end

Any suggestions?

[Mathias Pedersen Heinze]

Hi River,

I came up with this solution. It's probably not the most efficient, since it requires some line of code, but it seems to get the job done. The trick used here is to reshape the data and work with it in long format instead of in wide format. I find data management easier in long format in general.

* Generating an auxiliary index variable
gen index = _n

* Adding an identical prefix to all variables
foreach var of varlist onehf-threehf {
    rename `var' prefix_`var'
}

* Reshaping to long data format
reshape long prefix_, i(index) string

* generating variable m with label from highest value
by index (prefix_), sort : gen m = _j if _n == _N
by index, sort : replace m = m[_N] if m == ""

* Reshaping back to wide data format
reshape wide prefix_ , i(index) j(_j) string

* Removing prefix from variable names
foreach var of varlist prefix_* {
       local newname = substr("`var'", 8, .)
       rename `var' `newname'
}

* Dropping auxiliary index variable
drop index

I hope this will help you, or at least give you some ideas. The result looks like this

. list

     +-----------------------------------+
     | onehf   threehf   twohf         m |
     |-----------------------------------|
  1. |     1         3       2   threehf |
  2. |     4         5       6     twohf |
  3. |     9         8       7     onehf |
     +-----------------------------------+

[Andrew Musau]

You could also:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(onehf twohf threehf)
1 2 3
4 6 5
9 7 8
end


local vars " onehf twohf threehf "
egen m2= rowmax( `vars' )
gen m=""

foreach var of local vars { 
replace m = "`var'" if m2==`var'
}
drop m2
l

     +-----------------------------------+
     | onehf   twohf   threehf         m |
     |-----------------------------------|
  1. |     1       2         3   threehf |
  2. |     4       6         5     twohf |
  3. |     9       7         8     onehf |
     +-----------------------------------+

[River Huang]

@Mathias and @Andrew: Thanks a lot for your helpful suggestions. They work joust fine.

[Daniel Sutcliffe]

Hello,

So I wrote some code based on a previous post with a similar problem. However, it only seems to populate the variable name for some observations:

local vars "neurosurg_provider_RTH neurosurg_provider_RJE neurosurg_provider_RWW neurosurg_provider_RAP neurosurg_provider_RRK neurosurg_provider_RWA neurosurg_provider_RRV neurosurg_provider_RET neurosurg_provider_RR8 neurosurg_provider_RTR neurosurg_provider_RF4 neurosurg_provider_RVJ neurosurg_provider_RJ7 neurosurg_provider_RBN neurosurg_provider_RXH neurosurg_provider_RTD neurosurg_provider_RHQ neurosurg_provider_RGT neurosurg_provider_RYJ neurosurg_provider_RX1 neurosurg_provider_RLT neurosurg_provider_RXN neurosurg_provider_R1E neurosurg_provider_R1H neurosurg_provider_RKB neurosurg_provider_RM2 neurosurg_provider_RHM neurosurg_provider_RJZ neurosurg_provider_RK9 neurosurg_provider_RM3"

egen m2= rowmax( `vars' )
gen m=""

foreach var of local vars {
replace m = "`var'" if m2==`var'


}
drop m2

Does anyone know what is happening and how to amend the code?

The output is below:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 GPCode double(neurosurg_provider_RTH neurosurg_provider_RJE neurosurg_provider_RWW neurosurg_provider_RAP neurosurg_provider_RRK neurosurg_provider_RWA neurosurg_provider_RRV neurosurg_provider_RET neurosurg_provider_RR8) str22 m
"N85040"                    0                    0 0 0                    0                    0                   0                   1                    0 "neurosurg_provider_RET"
"B87040"                    0                    0 0 0                    0                    0                   0                   0    .8666666666666667 ""                      
"D81035"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RGT"
"G85082"                    0                    0 0 0                    0                    0                  .2                   0                    0 ""                      
"D83053"                    0                    0 0 0                    0                    0                   0  .14285714285714285                    0 ""                      
"A83045"                    0                    0 0 0                    0                    0                   0                   0  .022727272727272728 ""                      
"A86026"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RTD"
"A87003"                    0                    0 0 0                    0                    0                   0 .022727272727272728                    0 ""                      
"B81666"                    0                    0 0 0                    0    .8571428571428571                   0                   0                    0 ""                      
"B86018"                    0                    0 0 0                    0                    0                   0                   0    .9523809523809523 ""                      
"K81040"    .9411764705882353                    0 0 0                    0                    0                   0                   0                    0 ""                      
"M86015"   .09090909090909091                    0 0 0                    0                    0                   0                   0                    0 ""                      
"J81053"                    0                    0 0 0                    0                    0  .14285714285714285                   0                    0 ""                      
"B82106"                    0                    0 0 0                    0    .8571428571428571                   0                   0                    0 ""                      
"F83045"                    0                    0 0 0                    0                    0   .8275862068965517                   0                    0 ""                      
"C87620"                    0                    0 0 0                    0                    0                   0                   0  .045454545454545456 ""                      
"D81013"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"G83009"                    0                    0 0 0                    0                    0  .08571428571428572                   0                    0 ""                      
"M92649"                    0   .16666666666666666 0 0    .8333333333333334                    0                   0                   0                    0 ""                      
"N84017"                    0                    0 0 0                    0                    0                   0   .9770114942528736                    0 ""                      
"L83071"   .02127659574468085                    0 0 0                    0                    0  .02127659574468085                   0                    0 ""                      
"C84131"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"G83049"                    0                    0 0 0                    0                    0 .022727272727272728                   0                    0 ""                      
"E81009"   .08333333333333333                    0 0 0                    0                    0                .625                   0                    0 "neurosurg_provider_RRV"
"A87015"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"B85026"                    0                    0 0 0                    0                    0                   0                   0    .7419354838709677 ""                      
"B86052"                    0                    0 0 0                    0                    0                   0 .047619047619047616    .9285714285714286 ""                      
"G82046"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RJZ"
"G82051"                    0                    0 0 0                    0                    0                .075                   0                    0 ""                      
"H82089"                    0                    0 0 0                    0                    0                 .04                   0                    0 ""                      
"M92007"                    0   .11764705882352941 0 0    .8235294117647058                    0                   0                   0                    0 ""                      
"P82007"                    0                    0 0 0                    0                    0 .008547008547008548 .017094017094017096                    0 ""                      
"F86692"                    0                    0 0 0                    0                    0  .05714285714285714                   0                    0 ""                      
"K82618"                    1                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RTH"
"L81008"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RVJ"
"C83045"  .022727272727272728                    0 0 0                    0   .11363636363636363 .022727272727272728                   0  .022727272727272728 ""                      
"C86011"                    0                    0 0 0                    0                    0  .02127659574468085                   0   .02127659574468085 ""                      
"D81065"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RGT"
"H83049"                    0                    0 0 0                    0                    0   .2857142857142857                   0                    0 ""                      
"M86005"   .13333333333333333                    0 0 0                    0                    0                   0                   0                    0 ""                      
"J82166"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"F82008"                    0                    0 0 0                    0                    0  .05102040816326531                   0                    0 ""                      
"L83113"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"M83024"                    0    .8378378378378378 0 0   .08108108108108109                    0                   0  .02702702702702703                    0 ""                      
"Y03661"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RHM"
"M84627"  .037037037037037035                    0 0 0  .037037037037037035                    0  .07407407407407407                   0                    0 ""                      
"M87020"  .038461538461538464  .038461538461538464 0 0    .8846153846153846                    0                   0                   0                    0 ""                      
"N81115"                    0                 .025 0 0                    0                    0                   0                .925                    0 ""                      
"A82034"                    0                    0 0 0                    0                    0 .008547008547008548  .02564102564102564  .008547008547008548 ""                      
"J82053"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RHM"
"J83058"                    0                    0 0 0    .1111111111111111                    0   .1111111111111111                   0                    0 ""                      
"M81617"  .043478260869565216   .08695652173913043 0 0    .2608695652173913                    0                   0                   0                    0 ""                      
"N83001"                    0                    0 0 0                    0                    0                   0   .9056603773584906                    0 ""                      
"P82012"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"L81077"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RVJ"
"L81118"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RVJ"
"L82041"                    0                    0 0 0  .008695652173913044                    0  .02608695652173913                   0                    0 ""                      
"L83066"                    0                    0 0 0                    0                    0  .09090909090909091                   0                    0 ""                      
"M84064"                    0  .047619047619047616 0 0   .09523809523809523                    0 .047619047619047616                   0                    0 ""                      
"M88030"                    0                    0 0 0                    1                    0                   0                   0                    0 "neurosurg_provider_RRK"
"J81030"   .07142857142857142                    0 0 0                    0                    0                   0                   0                    0 ""                      
"J81616"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RHM"
"N82646"                    0                    0 0 0                    0                    0                   0   .9333333333333333                    0 ""                      
"K84060"    .7619047619047619                    0 0 0                    0  .047619047619047616                   0                   0                    0 ""                      
"L85025"                    0                    0 0 0                    0                    0  .08333333333333333                   0                    0 ""                      
"G85132"                    0                    0 0 0                    0                    0  .07692307692307693                   0                    0 ""                      
"H82009"   .01694915254237288                    0 0 0                    0                    0  .01694915254237288                   0                    0 ""                      
"B82005"                    0                    0 0 0  .004424778761061947    .6769911504424779 .004424778761061947                   0   .24336283185840707 ""                      
"E85677"                    0                    0 0 0                    0                    0  .16666666666666666                   0                    0 ""                      
"F82679"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"F86712"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_R1H"
"P87017"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RM3"
"Y00110"                    0                    0 0 0                    0                    0                   0                   1                    0 "neurosurg_provider_RET"
"C83018"                    0                    0 0 0                    0   .39473684210526316                   0                   0   .02631578947368421 ""                      
"D83070"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"H81025"                    0                    0 0 0                    0                    0                 .04                   0                    0 ""                      
"A82060" .0020477815699658703 .0013651877133105802 0 0 .0006825938566552901 .0006825938566552901 .004095563139931741 .031399317406143344 .0020477815699658703 ""                      
"A85011"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"K81050"                  .75                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RTH"
"C88032"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RHQ"
"M83066"                    0    .8636363636363636 0 0  .022727272727272728                    0                   0 .022727272727272728                    0 ""                      
"H81064"                    0                    0 0 0                    0                    0  .08333333333333333                   0                    0 ""                      
"A87008"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RTD"
"N82104"                    0                    0 0 0                    0                    0                   0                   1                    0 "neurosurg_provider_RET"
"P83020"                    0 .0037593984962406013 0 0                    0                    0                   0 .011278195488721804                    0 ""                      
"C81009"                    0                    0 0 0  .058823529411764705                    0                   0                   0                    0 ""                      
"F82003"                    0                    0 0 0                    0                    0 .041666666666666664                   0                    0 ""                      
"P91018"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"G84032"                    0                    0 0 0                    0                    0  .03333333333333333                   0                    0 ""                      
"A81038"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RTR"
"A82650"                    0                    0 0 0                    0                    0                   0  .03076923076923077                    0 ""                      
"L83011"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"C84105"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"G82754"                    0                    0 0 0                    0                    0                   0                   0                    0 "neurosurg_provider_RJZ"
"M83041"  .041666666666666664                    0 0 0    .9166666666666666                    0                   0                   0                    0 ""                      
"P82004"                    0                    0 0 0                    0                    0                   0                   0                    0 ""                      
"L83100"                    0                    0 0 0                    0                    0 .011583011583011582                   0                    0 ""                      
"C83013"                    0                    0 0 0   .03333333333333333   .06666666666666667                   0                   0                    0 ""                      
"C84660"                    0                    0 0 0   .09090909090909091                    0                   0                   0                    0 ""                      
"C85007"                    0                    0 0 0                    0                    0                   0                   0   .05555555555555555 ""                      
end

[Andrew Musau]

You are being bitten by precision. As your variables are stored in double precision, you need

egen double m2= rowmax(neurosurg_provider_RTH- neurosurg_provider_RR8)
gen m=""
foreach var of varlist neurosurg_provider_RTH - neurosurg_provider_RR8{
    replace m = "`var'" if m2==`var'
}
drop m2

Res.:

. tab m

                     m |      Freq.     Percent        Cum.
-----------------------+-----------------------------------
neurosurg_provider_RET |         14       14.00       14.00
neurosurg_provider_RJE |          2        2.00       16.00
neurosurg_provider_RR8 |         37       37.00       53.00
neurosurg_provider_RRK |          9        9.00       62.00
neurosurg_provider_RRV |         25       25.00       87.00
neurosurg_provider_RTH |          7        7.00       94.00
neurosurg_provider_RWA |          6        6.00      100.00
-----------------------+-----------------------------------
                 Total |        100      100.00

[Daniel Sutcliffe]

Thank you so much...that works perfectly!

[Nick Cox]

Some inefficient duplication of posts here. It is good practice to avoid cross-posting and to flag it otherwise.

Compare https://www.statalist.org/forums/for...e-one-variable

The other thread includes a link to a 2020 paper https://journals.sagepub.com/doi/pdf...36867X20931007 -- which was necessarily not available when this thread started.

It covers some variations on the theme of looping over variables to check which is equal to a previously calculated maximum. The technique for finding which is equal to a minimum is naturally similar.

[Johanna Krenz]

You could also:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(onehf twohf threehf)
1 2 3
4 6 5
9 7 8
end


local vars " onehf twohf threehf "
egen m2= rowmax( `vars' )
gen m=""

foreach var of local vars {
replace m = "`var'" if m2==`var'
}
drop m2
l

 +-----------------------------------+
| onehf twohf threehf m |
|-----------------------------------|
1. | 1 2 3 threehf |
2. | 4 6 5 twohf |
3. | 9 7 8 onehf |
+-----------------------------------+

I tried what Andrew suggested, however, I have quite a few variables from which I want to collect the max. They all start with b, therefore, I wrote:

local vars " b* "
egen m2= rowmax( `vars' )
gen m=""

foreach var of local vars {
replace m = "`var'" if m2==`var'
}
drop m2

However, I get an error message: invalid syntax
r(198);

[Andrew Musau]

You need

foreach var of varlist b*{

directly. In your syntax, Stata examines the local for variable names and finds only "b*", which is not a valid variable name.