First 5 maxima of each variable

[Federico Nutarelli]

Hi all,

I have a dataset looking as follows:

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 country2 float(abw dummy_max_abw zwe dummy_max_zwe usa dummy_max_usa)
"abw"            1 0 .0019718756 0  .13687219 0
"and"   .014349923 0  .006158919 0  .07704803 0
"are"     .1185484 1   .04283692 0  .20188904 0
"arg"   .005725511 0   .03825948 0  .14466025 0
"aus"    .02018883 0   .07614786 0    .168773 0
"aut"    .03312805 0   .00916558 0  .29010522 0
"aze"    .03398381 0   .03976445 0  .11621744 0
"bel"    .04820335 0    .0382955 0   .4047536 1
"ben"   .017073806 0  .012839578 0  .09850471 0
"bgd"   .003436714 0  .007515666 0 .010320474 0
"bgr"   .018050177 0  .027716434 0  .12128054 0
"bhr"   .015178327 0  .002306785 0   .0607749 0
"bhs"    .01491092 0  .004802156 0 .035464723 0
"blr"    .01225459 0  .003648403 0  .12491448 0
"bmu"   .008849956 0 .0004179719 0 .031641748 0
"bol"   .003068668 0   .01109194 0  .06246216 0
"bra"    .02413945 0   .09663036 1   .2344657 0
"brb"  .0040864744 0 .0003497313 0   .1294802 0
"brn"    .02044539 0 .0003099764 0  .04261869 0
"can"    .05574494 0  .028963504 0  .26776928 0
"che"    .05305018 0   .02348231 0  .20261946 0
"chl"   .006736322 0  .004417482 0  .12266704 0
"chn"    .03210985 0  .018137882 0   .3228953 0
"cog"   .003026098 0  .004340283 0  .01407715 0
"col"   .034652606 0   .05099791 0  .10408207 0
"cri"    .01294363 0   .00927898 0   .0843811 0
"cub"     .1147518 0   .01448861 0  .02099695 0
"cze"    .05393007 0   .01554409 0   .3298503 0
"deu"    .10929185 0  .031224016 0  .56809074 1
"dnk"   .025252383 0   .02579149 0   .2326925 0
"dom"     .1784329 1   .07229592 0  .07785798 0
"dza"    .03770436 0 .0015568928 0 .036913205 0
"ecu"   .003352564 0  .015833925 0 .022118226 0
"egy"   .008810362 0   .02295754 0  .13327245 0
"esp"    .05327821 0   .03655011 0   .3086794 0
"est"     .0319333 0  .005713239 0  .17999592 0
"fin"    .02198167 0   .03052579 0   .1904395 0
"fra"     .2012817 1  .037149053 0   .4824109 1
"gbr"     .1818118 1  .026991794 0   .5097138 1
"geo"    .05319497 0   .02130521 0  .12976152 0
"gha"   .005978563 0  .009540014 0  .02318415 0
"grc"    .02514978 0   .06185427 0   .1604421 0
"gtm"   .007533598 0   .04711339 0 .026629506 0
"hkg"    .07023658 0  .026208015 0   .1770199 0
"hnd"   .028283235 0   .01246677 0  .06733552 0
"hrv"   .030624226 0  .013970812 0  .18462183 0
"hun"    .03245282 0  .009954663 0    .244016 0
"idn"    .04929613 0   .04789947 0   .1044859 0
"ind"    .02264605 0    .0893084 0   .2272387 0
"irl"    .05808725 0 .0045877635 0  .17398465 0
"irn"   .007714055 0   .01411766 0  .05070183 0
"isl"   .011332856 0  .009554878 0  .05993953 0
"isr"   .034294844 0  .009605213 0  .22741705 0
"ita"     .0526498 0  .032576207 0   .3393518 0
"jor"    .00960455 0   .01006583 0   .0746403 0
"jpn"    .04872635 0   .03055816 0   .4041953 1
"kaz"   .008448931 0  .064389735 0  .07144289 0
"ken"   .013022172 0    .1180769 1  .04987016 0
"kgz"   .008970213 0   .04056296 0  .01986637 0
"kor"    .05246479 0   .01063813 0   .3165603 0
"kwt"    .00908643 0 .0011719033 0 .034646224 0
"lbn"    .03672688 0   .02623466 0  .07643057 0
"lka"   .018210942 0  .026407905 0   .0447094 0
"ltu"     .0899284 0  .014658935 0   .1988091 0
"lva"    .05082305 0  .011963298 0  .11922359 0
"mar"   .010582856 0   .04582751 0  .05075128 0
"mex"     .0543058 0   .03223966 0  .26961735 0
"mli"  .0011719106 0   .05885683 0 .068179905 0
"mlt"    .02101889 0   .11472948 1  .08213118 0
"mng" .00013223232 0  .020057956 0  .00940318 0
"mus"    .01127539 0   .01068087 0  .07512628 0
"mwi"  .0008504618 0    .5153678 1 .036532808 0
"mys"    .04732974 0  .022199925 0   .1784982 0
"ner"  .0008459404 0 .0033932775 0 .017192857 0
"nga"    .02131259 0  .026345823 0 .031623375 0
"nic"   .028114226 0  .015046704 0  .04994908 0
"nld"    .05395059 0   .02484862 0   .3011497 0
"nor"   .030980743 0   .03792192 0   .1503698 0
"nzl"   .007877593 0   .01307269 0  .10246637 0
"omn"    .03939246 0  .072956406 0  .08882053 0
"pak"   .009829223 0   .06420079 0  .09204338 0
"pan"   .015289637 0  .002389882 0  .17846557 0
"per"   .005001794 0   .01446719 0  .11403412 0
"phl"   .014968532 0   .04854037 0   .0925047 0
"pol"    .09486014 0  .028569816 0   .3297917 0
"prt"   .016771384 0   .01116554 0  .08521868 0
"pry"   .008301177 0  .012340585 0  .14989828 0
"qat"    .03734014 0  .000633485 0  .06208181 0
"rou"    .04362541 0  .017462866 0   .1740581 0
"rus"   .031332653 0  .032779995 0   .2059008 0
"sau"    .04066458 0  .008262284 0  .16364266 0
"sdn"  .0007801547 0  .007552347 0  .02204703 0
"sgp"     .0847599 0  .028863026 0   .2932844 0
"slv"   .016379204 0   .00980146 0  .09248148 0
"som" .00052251737 0  .017629985 0 .009312285 0
"srb"     .0395533 0   .02773232 0  .20327024 0
"svk"     .0469281 0   .02706082 0  .25369662 0
"svn"    .04905778 0  .021877887 0   .2750631 0
"swe"    .04173689 0  .017888274 0   .3319012 0
"syr"    .00628714 0  .005528532 0  .02027437 0
end

where the dummies indicate the first 5 maxima of each variable (abw, zwe, usa) and country2 is the name of the country against which I have computed the correlation (displayed in the variables abw, zwe and usa).
What I would like to do is to output a file in which I have basically 3 variables, abw_max, zwe_max, and usa_max having the names of the top 5 countries (i.e. those for which dummy =1).
In the above case the output should look as follows:

input str3(abw  usa zwe)
 "fra"  "deu"  "mwi"
"gbr"  "gbr" "zaf"
"dom"  "fra" "ken"
"usa"   "bel" "mlt"
"are"   "jpn" "bra"

where the names reported are respectively those names having dummy_max_ equal to 1 for those countries (or said otherwise the first five if you perform a gsort -abw, gsort -usa, gsort -zwe and exclude the 1 correlation that each country has with itself).

N.B. due to dataex constraints I was not able to report all the values for which the dummy is equal to 1. For that example, feel free to add ones to the dummy variables until you reach five ones. The idea is that the variable names corresponding to those ones should end up in the final database as shown above.


Thank you

[William Lisowski]

drop dummy_*
ds country2, not
rename (`r(varlist)') (val_=)
reshape long val_, i(country2) j(country1) string
drop if country2==country1
gsort country1 -val_ country2
by country1: generate rank = _n
keep if rank<=5
reshape wide country2 val_, i(rank) j(country1) string
rename (country2*) (*)

. list, noobs

  +---------------------------------------------------------+
  | rank   abw    val_abw   usa    val_usa   zwe    val_zwe |
  |---------------------------------------------------------|
  |    1   fra   .2012817   deu   .5680907   mwi   .5153678 |
  |    2   gbr   .1818118   gbr   .5097138   ken   .1180769 |
  |    3   dom   .1784329   fra   .4824109   mlt   .1147295 |
  |    4   are   .1185484   bel   .4047536   bra   .0966304 |
  |    5   cub   .1147518   jpn   .4041953   ind   .0893084 |
  +---------------------------------------------------------+

N.B. due to dataex constraints...

The limit of 100 observations is a dataex default, not a dataex constraint. The count() option changes the default and using it would have allowed presentation of all the observations.

[Federico Nutarelli]

Thank you very much!

[Federico Nutarelli]

Thank you very much!