Create a new variable by taking the maximum value of a var list

[Sophie Nguyen]

Hi All,

May I ask for your help with my problem? I have a data set as below. This data is about multinational firms. I want to figure out the nationality of the firm (x5) depending on the share of investors (xvn, x1, x2, x3, x4, x5). Nationality ( country1, country2, country3, country4) will be of the investor with the biggest share.

For each id (tax_code), I want to create a new variable (X5), which depends on the maximum value among 5 variables: xvn, x1, x2, x3, x4. To be specific:
If xvn is the maximum, x5 takes the value "VN" (VN is a value, not a variable name).
if x1 is the maximum, x5 takes the value OF variable"country1"
if x2 is the maximum, x5 takes the value OF variable "country2"
if x3 is the maximum, x5 takes the value OF variable "country3"
if x4 is the maximum, x5 takes the value OF variable "country4"

I read some similar posts but still has not figured out. Thank so much and look forward to your help.

All the best,

Nhung

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 tax_code str15 Country1 str9 country2 byte(country3 country4) double(xvn x1 x2) byte(x3 x4)
"1101771307" "Singapore"       ""          . .                   0                     1                 0 0 0
"3603078259" "Japan"           ""          . .                   .                     1                 . . .
"1101799768" "Japan"           ""          . .  .39393940567970276     .6060606241226196                 0 0 0
"0107165501" "South Korea"     ""          . .                   0                     1                 0 0 0
"3702304847" "Taiwan"          ""          . .                   0                     1                 0 0 0
"1101783983" "France"          ""          . .                   0                     1                 0 0 0
"1300980577" "Hong Kong"       ""          . .    .699999988079071    .30000001192092896                 . . .
"2901629703" "Thailand"        ""          . .                   .                     1                 . . .
"1601961289" "South Korea"     ""          . .                   .                     1                 . . .
"1100672328" "Taiwan"          ""          . .                   0                     1                 0 0 0
"3702529784" "Taiwan"          ""          . .                   0                     1                 0 0 0
"4201761192" "South Korea"     ""          . .                   .                     1                 . . .
"1101834557" "Taiwan"          ""          . .                   0                     1                 0 0 0
"0314901975" "Norway"          ""          . .                   0                     1                 0 0 0
"3502355146" "South Korea"     ""          . .                   .                     1                 . . .
"3200622934" "France"          ""          . .                   .                     1                 . . .
"1101858734" "South Korea"     ""          . .                   0                     1                 . . .
"4700269589" "Japan"           ""          . . .046680498868227005     .9533194899559021                 . . .
"5801194613" "South Korea"     ""          . .                   .                     1                 . . .
"0201746020" "China"           ""          . .                   .                     1                 . . .
"1101853768" "China"           ""          . .                   0                     1                 . . .
"4500596765" "United Kingdom"  ""          . .   .6999485492706299     .3000514805316925                 . . .
"3603571270" "Taiwan"          ""          . .                   .                     1                 . . .
"1101837420" "China"           ""          . .                   0                     1                 0 0 0
"3801099412" "United States"   ""          . .   .9015337228775024    .09846625477075577                 . . .
"3702363056" "Taiwan"          ""          . .                   0                     1                 0 0 0
"3702430087" "Taiwan"          ""          . .                   0                     1                 0 0 0
"3700669916" "Indonesia"       ""          . .                   0                     1                 0 0 0
"6300250202" "China"           ""          . .                   .                     1                 . . .
"3801119468" "China"           ""          . .                   .                     1                 . . .
"2801960710" "Singapore"       ""          . .                   .                     1                 . . .
"3603289387" "Taiwan"          ""          . .                   .                     1                 . . .
"0108340682" "China"           ""          . .                   0                     1                 0 0 0
"1101805531" "Taiwan"          ""          . .                   0                     1                 0 0 0
"1801488768" "China"           ""          . . .010004275478422642     .9899957180023193                 . . .
"0313258303" "Singapore"       "Samoa"     . .                   0                    .5                .5 0 0
"1101813388" "Australia"       ""          . .                   0                     1                 . . .
"3502236798" "South Korea"     ""          . .                   .                     1                 . . .
"5701591543" ""                "Singapore" . .                   .     .5099961757659912 .4900038242340088 . .
"1101856945" "Taiwan"          ""          . .                   0                     1                 . . .
"3603505912" "Japan"           ""          . .                   .                     1                 . . .
"3603372772" "Japan"           ""          . .                   .                     1                 . . .
"0313736729" "Japan"           ""          . .                   0                     1                 0 0 0
"3702139167" "United Kingdom"  ""          . .                   0                     1                 0 0 0
"0107547081" "Japan"           ""          . .   .6399999856948853    .36000001430511475                 0 0 0
"3603361812" "Japan"           ""          . .                   .                     1                 . . .
"0314303541" "Japan"           ""          . .                   0                     1                 0 0 0
"1101836233" "South Korea"     ""          . .                   0                     1                 . . .
"0401911553" "South Korea"     ""          . .                   .                     1                 . . .
"3702555872" "Thailand"        ""          . .                   0                     1                 0 0 0
"0313209070" "Japan"           ""          . .   .5053078532218933     .4946921467781067                 . . .
"0900178525" "United States"   ""          . .                   .                     1                 . . .
"0401664329" "Japan"           ""          . .  .12780898809432983     .8721910119056702                 . . .
"3502332050" "Japan"           ""          . .                   .                     1                 . . .
"1101792642" "Taiwan"          ""          . .                   0                     1                 0 0 0
"3701671587" "The Netherlands" ""          . .                   0                     1                 0 0 0
"1101793269" "South Korea"     ""          . .                   0                     1                 0 0 0
"1101795153" "Japan"           ""          . .                   0                     1                 0 0 0
"0201802370" "China"           ""          . .                   .                     1                 . . .
"3702257160" "France"          ""          . .                   0                     1                 0 0 0
"0313594753" "Singapore"       ""          . .  .48996102809906006     .5100389719009399                 0 0 0
"0700797694" "Japan"           ""          . .                   .                     1                 . . .
"3702173626" "Taiwan"          ""          . .                   0                     1                 0 0 0
"3603567549" "Japan"           ""          . .                   .                     1                 . . .
"3700424923" "Russia"          ""          . .   .9999463558197021 .00005366822369978763                 0 0 0
"0313511242" "South Korea"     ""          . .                   0                     1                 0 0 0
"2901777010" "Japan"           ""          . .  .24999558925628662     .7500044107437134                 . . .
"0201761149" "Malaysia"        ""          . .   .4888888895511627     .5111111402511597                 . . .
"3600817381" "United States"   ""          . .                   0                     1                 0 0 0
"0700777867" "United States"   ""          . .                   .                     1                 . . .
"0801228951" "Singapore"       ""          . .                   .                     1                 . . .
"0303096565" "Taiwan"          ""          . .                   0                     1                 0 0 0
"3502299325" "South Korea"     ""          . .                   .                     1                 . . .
"1501066067" "Singapore"       ""          . .                   .                     1                 . . .
"0801191758" "United States"   ""          . .                   .                     1                 . . .
"1801319745" "Marshall island" ""          . .                   .                     1                 . . .
"0700779173" "South Korea"     ""          . .                   .                     1                 . . .
"2300973412" "Hong Kong"       ""          . .                   .                     1                 . . .
"3701091716" "The Netherlands" ""          . .                   .                     1                 . . .
"3603304564" "Hong Kong"       ""          . .                   .                     1                 . . .
"1101844636" "China"           ""          . .                   0                     1                 . . .
"3603238590" "United States"   ""          . .  .49015647172927856     .5098435282707214                 . . .
"0801192663" "Singapore"       ""          . .                   .                     1                 . . .
"1402031401" "Australia"       ""          . .   .9650105237960815   .034989502280950546                 . . .
"1101799292" "China"           ""          . .                   0                     1                 0 0 0
"1101842318" "Taiwan"          ""          . .   .4897959232330322     .5102040767669678                 0 0 0
"5400448388" "China"           ""          . .                   .                     1                 . . .
"3602958772" "Malaysia"        ""          . .                   .                     1                 . . .
"1201548655" "China"           ""          . .                   .                     1                 . . .
"0314628282" "France"          ""          . .                   0                     1                 0 0 0
"1402078946" "Singapore"       ""          . .                   .                     1                 . . .
"1201525697" "Singapore"       ""          . .                   .                     1                 . . .
"1300942250" "Australia"       ""          . .                   .                     1                 . . .
"1101796904" "China"           ""          . .                   0                     1                 . . .
"3502229769" "Cambodia"        ""          . .                   .                     1                 . . .
"1101788011" "United States"   ""          . .                   0                     1                 0 0 0
"3702486509" "Australia"       ""          . .                   0                     1                 0 0 0
"3702070437" "The Netherlands" ""          . .                   0                     1                 0 0 0
"1101828930" "Singapore"       ""          . .                   0                     1                 0 0 0
"0401844297" "Japan"           ""          . .                   .                     1                 . . .
end

------------------ copy up to and including the previous line ------------------

Listed 100 out of 105 observations
Use the count() option to list more



[/CODE]

[Hemanshu Kumar]

Try this:

tostring country3 country4, replace // only because in this data extract, these two variables are empty and created numeric

gen maxval = max(xvn,x1,x2,x3,x4)
gen wanted = cond(maxval == xvn, "VN", ///
                cond(maxval == x1, Country1, ///
                cond(maxval == x2, country2, ///
                cond(maxval == x3, country3, country4))))
drop maxval

where in your actual data you probably don't need the tostring line.

A sample of the output:

. li in 21/40, noobs

  +---------------------------------------------------------------------------------------------------------------------------+
  |   tax_code         Country1    country2   country3   country4         xvn          x1          x2   x3   x4        wanted |
  |---------------------------------------------------------------------------------------------------------------------------|
  | 1101853768            China                      .          .           0           1           .    .    .         China |
  | 4500596765   United Kingdom                      .          .   .69994855   .30005148           .    .    .            VN |
  | 3603571270           Taiwan                      .          .           .           1           .    .    .        Taiwan |
  | 1101837420            China                      .          .           0           1           0    0    0         China |
  | 3801099412    United States                      .          .   .90153372   .09846625           .    .    .            VN |
  |---------------------------------------------------------------------------------------------------------------------------|
  | 3702363056           Taiwan                      .          .           0           1           0    0    0        Taiwan |
  | 3702430087           Taiwan                      .          .           0           1           0    0    0        Taiwan |
  | 3700669916        Indonesia                      .          .           0           1           0    0    0     Indonesia |
  | 6300250202            China                      .          .           .           1           .    .    .         China |
  | 3801119468            China                      .          .           .           1           .    .    .         China |
  |---------------------------------------------------------------------------------------------------------------------------|
  | 2801960710        Singapore                      .          .           .           1           .    .    .     Singapore |
  | 3603289387           Taiwan                      .          .           .           1           .    .    .        Taiwan |
  | 0108340682            China                      .          .           0           1           0    0    0         China |
  | 1101805531           Taiwan                      .          .           0           1           0    0    0        Taiwan |
  | 1801488768            China                      .          .   .01000428   .98999572           .    .    .         China |
  |---------------------------------------------------------------------------------------------------------------------------|
  | 0313258303        Singapore       Samoa          .          .           0          .5          .5    0    0     Singapore |
  | 1101813388        Australia                      .          .           0           1           .    .    .     Australia |
  | 3502236798      South Korea                      .          .           .           1           .    .    .   South Korea |
  | 5701591543                    Singapore          .          .           .   .50999618   .49000382    .    .               |
  | 1101856945           Taiwan                      .          .           0           1           .    .    .        Taiwan |
  +---------------------------------------------------------------------------------------------------------------------------+

Of course, the code is not well defined if the maximum value is not unique in a row. You will need to tell us if your actual data ever has this problem, and if so, how you would want to resolve those cases.

[Sophie Nguyen]

Thank you so much for your reply Hemanshu Kumar ! My data does not have that problem! Really appreciate your help!

[Sophie Nguyen]

Hi. Can you help me check again Hemanshu Kumar ? I typed the command you suggested and got some errors. Thank you very much!
gen wanted = cond(maxval == xvn, "VN", /// there is error: too few ')' or ']' and . cond(maxval == x1, Country1, /// command cond is unrecognized r(199); . . cond(maxval == x2, country2, /// command cond is unrecognized r(199); . . cond(maxval == x3, country3, country4)))) command cond is unrecognized r(199);

[Hemanshu Kumar]

It looks like you might be typing the command interactively from the command window. Put it in a do-file and it should work fine. Alternatively, remove all the /// from the gen command and join its multiple lines together, and that will work from the command window,.

[Sophie Nguyen]

It works. Thank you so much.