identifying variable with maximum value among a varlist

[charlie wong]

Hello,

I would like to find out for each observation, which variable has the highest value among a list of variables, and then store the name of the variable in a new variable.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double date float(n1_21 n1_242 n1_246 n1_247 n1_256 n1_264 n1_265 n1_267 n1_2043 n1_2107 n1_2123 n1_2124 n1_2136 n1_2222 n1_2229 n1_2370)
20212 -.0045941495  -.004157941    -.0378455     -.015894 .            0  -.003160556   -.026315736  .0044776434  .010711352  -.008358709 .   -.01877649    .002692957 .  .025000015
20213   .010512836   -.04592903 -.0044883303    .01884245 .    .07503073  -.011414097    .013047473  -.022288276 -.019021785 -.0030650874 .    -.0148148    -.01029538 . -.019512177
20214  -.020553173  -.008752769  -.019687414  -.017172992 .   -.04576659  -.017960154   -.007359663  -.027355596 .0005540425 -.0007686864 .   -.02819549   -.025780244 . -.018242145
20215    .01917097  -.007726236   .015483682   .010752627 .  -.014388544   .008491103     .02594991   .035937503   .00775197  -.001538438 .    .02901354   .0046425257 .   .07145268
20216   -.02541942   .005561735  -.019021725   .015514253 .    .01338207   .027202154    .009033424    .04449473  -.03846157 -.0023112716 .   .020676663   -.004621072 .     .054706
20219  -.007303046   -.00221244  -.016620526  -.021824596 .    -.0240096   -.01008831   -.006266759  -.025270784  .008571421  -.007722008 .            .   -.013463296 . -.004982556
20220   -.01050972   -.02439021  -.007511766  -.016064217 .   .008610048  .0015923567    -.02342341 -.0044444758   .00991504   .004669308 .            .      -.010353 .  .021532314
20221   .006903893   .009090944   -.04068113  -.009523768 .   .002438987     .0254372  -.0018450606   .019345256  .007012582   .006196652 .  -.015686259   .0017436233 .   .00808821
20222   .017404998   .009008957   .009861933   .017857078 .   .009732397   .006511647    .018484289  -.016058413 .0006963948 -.0084679825 . -.0039840788      .0348101 . -.013858466
20223   -.00777605  -.008928521   .026692674   -.01079616 .   .004819295  -.008626058   -.008166914  -.007418355   .01461378   .019215766 .  -.024000015    -.02935777 .   .02662719
20226   .003657326 -.0003217577   .023462275    .02592079 .   .015587475   .002486073     .02012806  -.005979068  .010973927  -.024185777 .    .02254103   -.008506631 .   .06484147
20227 -.0020822964   .018345661   -.00929368  .0021903059 .  -.002361239  -.018598884   .0080717625   .004511238   -.4628804  -.002341944 .  -.022044133   -.012256633 .   .06224629
20228   -.02503914   -.02654869  -.023452157  -.008819924 .   .007455589   -.00947568    .005338065   .008982028           .  .0003129962 . -.0040983763 -.00013784032 . -.031847134
20229   .011771066     .0181818            0     .0294512 .  .0008222683   .011479612     .05575224   -.04451035           .   .002033809 .    .04115226     .01930502 .  .035526313
20230  .0015863975  .0044643027   .029779045    .04031207 .    .05164321  .0015762924      .0259849  -.004658418           .  -.003122632 .   .017786592    .010416652 .           .
20234  -.021911267   .008888923  -.016324628   -.02208338 .            0 -.0044067497   -.006944432  -.026521074           .    .01527014 .  -.008414259    -.01218365 .           .
20235  -.018623466   -.04185025   -.04220007   -.04516403 .  -.015625017   -.01359465    -.04278072   -.01923075           .  -.005013451 .   -.02088771   -.019924084 .           .
20236  -.019802013  -.006896534  -.012871318  .0005950315 .  .0011338561  .0008546878   -.021057194   .009803913           .   -.00852712 .  -.010999985   -.008712502 .           .
20237   .015712699   .027777795     .0551655    .01679796 .   -.01472257  -.000853958    .024582926  -.003236243           .  .0007818489 .    .02932256    .007812455 .           .
20240  .0003683185 -.0022523038   -.01806078  -.020723645 .  -.022988506   .008333353   -.017566437     -.012987           .   .004687548 .  -.011787863   -.001937955 .           .
20241    .01095363    .00451469  -.008712502  -.020490447 .  -.011764706   .006992949   -.017880468   .003289471           .   .006220744 .    -.0059642    .014563107 .           .
20242  -.017754711 -.0022471568   -.03271483  -.002743547 .    .01309522  -.003156566   .0044404976 -.0032786855           .            0 .   .014000015    -.01531099 .           .
20243   .005561735 -.0045045214    -.0812721   -.02063274 .   .009988232  -.017521612   -.016504519  -.011513107           .  -.008500703 .  -.009861933   -.003887284 .           .
20244  -.008849591   .004524904    .02087914   .012640472 .   -.05177425  .0010743774     .02936766  -.011647283           . -.0007794708 .   .007968082   -.010731692 .           .
20247  -.007254481   .006756739  -.003229312  -.027565924 .  -.006134969 -.0019317647   -.031441033  -.031986542           .    -.0187207 .  -.017786516   -.023175526 .           .
20248  .0029230146   .003728504  .0084593175  -.015867291 .    .01234568  -.010967722      -.012624  -.026086973           .  -.017488113 .  -.018108683    -.01211514 .  -.02272725
20249   .011097386  -.028231794  -.008388358    .02463764 .  -.009756135   .008480124    .021004595            0           .    .00161816 .  -.008196674     .02299438 . -.011627896
20250    .01718407   -.01146789 -.0021597943    .02687414 .   .017241398   .018758046     .02862251    .01964288           .   .009693028 .    .04958673     .01398603 .  -.02352946
20251  -.012534076   .012374312   -.04220781  -.031336006 .  -.009685175  -.013333372   -.031304333  -.022767095           .  -.021599976 .  -.007873971   -.014449923 .  -.04819272
20254  -.007726236   -.02368219   -.04293789   -.02737295 .  .0024449504  -.007721988   -.016158016   -.02822577           .  .0040883073 .   -.03373017   -.010329885 .   .02531643
20255   .010381917    .01173709    -.0421094   .015107205 .   .003658574   .003080455     .01140518   .017980607           .   .004885981 .   .008213505      .0161616 .   .04938274
20256   .008440322  -.024941925   -.06306494  -.022083526 .   .007290382 -.0004848679   -.006765968   .009058005           .  -.008103727 .   .016293341   -.016567238 .  -.02352946
20257  .0021834562   -.02676978            .   -.01535868 .  .0036187626 -.0038810226    -.02270663   .017953305           .   .001633962 .  -.013026082     .00467812 .  -.01204818
20258    .01906318    .02444988            .   .029059807 .   .007211612     .0207792     .01394052  -.005291042           .   .021207165 .   .015228426     .02217231 .           0
20261   .008551528    .03818612            .    .01962196 . -.0023866894   .004664996     .01741523   .015957475           .   -.00559103 .   .028666686    .030511796 .   .06097563
20262  -.007949126   .027586224            . -.0014253864 .    -.0570175   .036196664    .008408418  -.013961592           .  -.016064256 . -.0006480782    -.01146129 .   .08045976
20263   .008012821  .0044742897            .   -.02326916 .    .01733608  -.011304558   -.012507458  -.035103317           .  -.000816314 .  -.009727626    -.01932367 . -.021276575
20264  -.005299417   -.04899779            .   -.03049784 .  -.022443835  -.012051897   -.036489744  .0018343435           .  -.005514731 .   -.02357565   -.015763532 .  .010869554
20265   -.02503994   .004683859            .  -.015577882 .   -.03826531   -.04432733    -.03774645   -.05889534           .  -.012528253 .  -.032193206   -.006506522 .  .032258034
20268   .006010962   .005827506            .   .029862126 .    .02387262   .013325895    .018912753    .04474709           .  .0008319975 .    .04573806   .0001678851 . -.010416657
20269   -.01321746  -.003476281            .  -.016208237 .  -.022020686   .007590459    -.02459518  -.014897565           .  -.000831306 .  -.009277602    .012424488 . -.003508769
20271  -.018532122   -.02674422            .    -.0220677 .   -.01986755  -.006251041   -.027982313   -.03213612           . -.0011647204 .   -.04682279  -.0039801146 .  -.02816899
20272  -.022994986   -.04420546            .    -.0247295 .    -.0280405   -.05419351    -.04065658    -.0800781           .  -.013826387 .   -.06596487   -.036713243 .  -.11956523
20275  -.001722176  -.005000019            .   -.02905439 .    .02050745  -.012960498    -.03886987  -.014862032           .  -.004222973 .   -.04883549   -.007000291 .  -.05555557
20276   -.06095452   -.06030145            .   -.08269857 .   -.03905543   -.06841738    -.06171259   -.03879307           .  -.020356247 .   -.08388627    -.06005222 .   -.3529412
20277    .04643802    .05561492            .    .04982205 .    .03900683    .08679523     .06596616    .07539245           .    .02510824 .    .11743406    .017499967 .    .3939394
20278   .006534685   .009929035            .    .06610172 .   .020678125   .016791854     .05038333    .01746151           .  -.009290528 .   .027777795     .04668307 .  -.01449274
20279   .011046476    .04396575            .    .01430839 .   .012032003   .009666938     .01633645   .009221327           .     .0349531 .  .0045044357             0 .    .1323529
20282    .03450259  -.011411374    -.0416982  -.010188052 .   -.00660502   .010970806    -.02205884  -.031472124           .    .02141679 .            0    .004694884 .    -.025974
20283  -.006948305 -.0036452375   -.05854432   -.02612829 .   .006648936  -.013482427    -.01591183   -.01572323           .  -.003225819 .   -.00672644    .009172703 .  -.05333336
20284  .0013993843   .021951256    .04537816   .012195121 .  -.010567972            0    .016417839   -.00958468           .  .0008090492 .    .04063204   .0017149455 .  .014084578
20285  -.002235852  -.014319863   -.01125403   .012048192 .   .021361794   .024666646     .01793588   .027957013           .   .006063056 .  -.006507576 -.00025682102 . -.013888958
20286 -.0039215516  -.007263904    -.0422764   -.00634923 . -.0039216084   .010830862    -.00850082  -.014644387           .   .002008839 .    .01703054      .0084768 . -.014084494
20289   .005624297   .012195121   .016977929   .016773213 .    .02077869   .006663634    .010132486  -.016454425           .   .007217334 .    .03048523     .03922566 .           0
20290  .0033556535  -.011244992   -.02337231  -.002356662 .  -.015213212  -.012468012   -.006481453  -.027522847           .   .017515961 .   -.04348953   -.009803922 .  -.08571429
20291  -.006688946  .0040617697 -.0045584003 -.0011811143 .            0 .00013870728    .018432213   .024139814           .   .010172168 .   .017152075  -.0019801678 .   .12500007
20292  -.010101028            0  -.010875817  -.028774155 .  -.007180097  -.018588644   -.013218053  -.005960374           .   .025561484 .  -.006423885   -.005952441 . -.027777834
20293  -.030045284   -.06504855   -.05729166   -.05478896 .   -.05325448   -.03990767    -.51416796   -.05042252           .  -.009063422 .   -.05603453    -.03493014 .   -.0142857
20296   .005844535   .010384156    .01473295   .004723083 .   .019444466   .005839854   -.026511135  -.013777254           .  -.001524367 .  -.020547893    .012668109 .   .01449274
20297   .003486292 -.0041109934   -.05263154   .010256384 .   -.01907359  -.013888834   -.009803938  -.001164171           .   .003816794 .  -.006993078    .009445958 .           0
20298  -.006948448    .01651195  -.009578544  -.021996604 .  -.009259224  -.010159372    -.01320137   .008158472           .   .015969628 .   .009389708    .009610567 .           0
20299    .00816323   .012690355  -.005802693  -.013840818 .  -.014485974   .010147042   -.006688946  -.028901735           .    .00748503 .   .009302361     .01503006 .           0
20300  -.008675535   .015037555    -.0389105  -.031578936 .    -.0412518 -.0024247044    -.02581364    -.0095238           .   .012629991 .   -.02073736    -.01727542 .  .028571486
20303 -.0087514585    .04444443    .01879696   .034420248 .   -.04154307  -.005295118     .01747307    .01923075           .  -.006603148 .    .01823533  -.0010045816 . -.013888958
20304  -.005885815   -.00472815   .007380091   .005253994 .     .0479876  -.006545074   -.006982473  -.002358432           .  -.002954165 .   .007510127    .014580238 .           0
20305   .003552434   -.04037997   -.01380672  -.012195135 .  -.002954165  -.009838434   -.013682976   -.02127663           .   -.01111111 .  -.020642236   -.007928672 .           0
20306  -.003539859  -.002475304   -.02199997   .012345692 .  -.010370325   .008516765    .018001666  -.002415399           .  .0037453184 .  -.009367717    .006993053 .  .014084578
20307 -.0011841146   .014888395   .006134953    .01254351 .   -.00748503   .004926087     .00957171    .01513317           .   .005970172 .    .00709218     .01289678 .   .06944437
20310  -.010077042   .004889901   .014227658   .015141106 .  .0015082725  -.014005602   .0007498948   .028026195           .            0 .   .009389708    .005550104 .   .05194808
20311 -.0011975865   -.02311428   -.04509018   -.02542373 .   -.00753012   -.04024625    -.02322966  -.020494927           .  -.003709199 .    -.0790698   -.023051893 .  -.04938274
20312   .007194226  -.006724888   -.02728233   .003478274 .    .01972679    .06709422 -.00028770455   -.08803795           .  .0022338275 .   .033670098   -.012961146 .   .02597408
20313  -.010714304  -.035105262   -.03703705  -.003466218 . -.0014880726   -.00624129   -.005467608   -.05844153           . -.0009906876 . -.0008143198     .00606059 .   .02531643
20314 -.0027676984  -.005197525   -.01867062  -.013043478 .   .002980694  -.009769778   -.010416684  -.002758684           .  -.003470448 .   -.01222494     .01405624 .  .024691334
20317   -.01713534   .035527706  -.020547893   -.03259909 .   -.00742942  -.017618041   -.029239766   -.05809123           .   .006716372 .  -.025742596   -.008580878 .  -.01204818
20318  -.005524825  -.006054437    .04662004   -.02641167 .   -.03892225   .002510804    -.02891568    .04127919           .  -.007412899 .   -.02489836   -.017310217 .  -.03658533
20319   -.02160494  .0010151548  -.035634793  -.015902756 .  -.024922095  -.025402525   -.017617844   -.02173304           .   -.03136667 .  -.016154319  -.0020325668 . -.063291155
20320   .017665634   -.03853957  -.017321017  -.024714814 .   -.00718845  -.033773795   -.020134933   -.02433588           . -.0026984646 .  -.014830445     .01849965 .   -.0135135
20321   -.03967768   -.04219405   -.05052873   -.05653017 .   -.03781178   -.05015202     -.0797275    -.0396239           .   -.04754549 .   -.05627242    -.07932005 .   -.2191781
20324  -.023240743 -.0022025886   -.04950495   .030991735 .    .00501671         .047     .02240894  -.018461522           .  .0008117378 .    .03911887    .005610815 . -.035087686
20325   -.04979075  -.017108137   -.03750006  -.016032124 .  -.009983336  -.017892336   -.016438315   -.03605016           .   .021897785 .   -.02083329    -.02753775 .  -.03636371
20326    .05935549    .04688372      .025974      .101833 .   -.02857144   .027748954     .05988851     .0280488           .   .014285739 .    .10694288     .04416737 .   .07547174
20327 -.0017509034    .01045855    .08913505 -.0036968014 .  -.010380597  -.011355033    -.01084096    .06128902           .   -.01330207 .  -.019726925   -.022978507 .   .08771932
20328   .007015998   .015923526   .002421363  -.007421178 .   -.05244755   .009188337    .008302889   -.05067066           .   .021411614 .  .0025800385     .03991289 . -.016129017
20331  -.009797517   -.05642633   -.05217391    -.0485981 .    -.0756458  -.035660125   -.018445343  -.014128678           .   -.02018638 .   -.04477616    -.03018142 . -.065573804
20332  -.016325897  -.019933484  -.030581057    .01021612 .            0    .00236037     -.0152125  -.023885366           .  .0039619654 .  -.002155119   -.009713993 .           0
20333  -.006873387   .009039539   -.00946374   -.01983665 .    .02994012  .0013081956   -.027848294    .03371394           .   .007103406 .   -.00971924   -.001634894 .  .017543843
20335  -.005954225   .005599019   .003184685  -.009920634 .  -.009689922  -.008361633    -.00313408   .004471308           .   .020376164 .  -.016357647   -.024017435 . -.034482725
20338   .015499542   .013363123     .1560847    .04809615 .   .026418833   .022924853     .05414907    .05891595           .    .01587301 .    .05728009    .024888126 .   .05357138
20339    .03244151     .0395604    .11029747    .05353727 .    .02573877   .031427093     .03491219   .014836852           .    .03881047 .    .04648717     .02701226 .           0
20340  -.013281522  -.008456651  -.032357786  -.025408307 .   -.00929368   -.03646344   -.009239272   -.04970763           .   -.02183406 .   -.05210416    -.02709878 .  .033898376
20341  -.012475437    .04093815   -.02966689   -.02420855 . -.0003752431  -.009720062   -.006397771    .01230768           .   -.00595229 .   .003171219             0 .  .016393427
20342  .0006649342  -.017820543   .007525959    .01908397 . -.0024398884   .003533545    .001855234  .0045593022           .  .0019959775 .  -.006322389  -.0021846485 .  .016129017
20345  -.019933555    -.0430136   -.02178649   .005617963 . -.0009407912    .02660402  -.0039216084  .0045385375           .    .00473106 .  -.015906742   -.013136256 .    .0238095
20346  -.002033919    .03840915   .015590132   .036312863 .   .003766493   .032012172     .01968504    .05120484           .            0 .   .012931023     .03782589 . -.007751931
20347            0     -.019937    .03947375    .01886791 .    .01938711  -.008862607    .015444075    .05873923           .   .005204438 .    -.0138297   -.020949194 . -.015624985
20348   .007472868   .014989228 -.0042194254   .017636685 .   .019631954   .007078964     .02281363    -.0189445           .    .02071008 .    .02049618      .0060044 .    -.031746
20349   .002022947    -.0400843  -.006355916  -.021952624 .  -.012635392  -.010728798   -.007434926   -.02758618           .   .010144884 .   .019027516    .010716174 .           0
20352   .009421224            0  -.027718615   -.01535739 .  .0018281257  -.012590384  -.0014044372  -.018439732           .   .007891006 .  .0020746372     .03986044 . -.016393427
20353  .0040000407  -.027472526   -.05701751  -.022795437 .     -.040146  -.007701017   -.015846249   -.03901734           .  -.016370129 .  -.032712214   -.013939066 .   -.0333334
20354  -.005976156   .001506482  -.018604634  -.018416207 .   .024714887 -.0022900996   -.015497983   -.00451131           .   .014109964 .  -.017551323   -.006806299 .   .03448283
end

In the above example, I would like to create a variable containing names of the variable that has the highest value among the varlist n1* , for each given date. I can think of using loops and rowsort, but wonder if there is a better way.

Grateful for advice.

[Marcos Almeida]

I gather - egen - plus rowmax() will fit your needs.

[charlie wong]

Marcos Almeida Thank you for the suggestion. My understanding is that rowmax gives the max value among the specified varlist. What I want is the name of the variable that gives the max value, and accordingly store the name in a variable. I can do loops to check which variable matches the max value, but wonder if there is a better way.

[Marcos Almeida]

Now I see.what you wish. The user-written (Nick Cox's) - rowranks - will probably do the trick.

[Nick Cox]

I know no better way than looping. The key point remains that in general variables can tie on which is maximum in a given observation, so a unique result is not guaranteed. That won't bite, I guess, in your case, but it's one reason why (for example) I never wrote such a program. rowranks (Stata Journal) is at best an indirect answer here.

That said, these look like panel data that would be better reshaped to long for most Stata purposes.

[charlie wong]

Nick Cox . Thanks for the reply. Personally I wouldn't mind if missing is reported in case of a tie ... but that of course is my preference and wish only.

[Nick Cox]

I've never thought that returning missing was an option under ties. But this illustrates the key point: there can be different answers here which is part of why there isn't (obviously) an agreed, public solution.