Constructing Internationalization variable

[Marnik Gun]

Dear statalist users,

I'm trying to construct an internationalization variable and for this I need to know the number of foreign countries a firm has subsidiaries in and the total number of foreign subsidiaries of the firm.
The data that I obtained from Orbis gives a total number of subsidiaries including the domestic ones, so that one is of no use unfortunately.
So I was wondering if someone knows a code so I can get variables that counts the countries per firm and a variable that counts the number of subsidiaries per firm.
Some of my data is stated below:

* Example generated by -dataex-. For more info, type help dataex
clear
input int companynumber str101 company double revenue long employees double(rd netincome totalassets) int subsidiaries double(export sales debt equity) long(country subcountry industrycode)
1 "VOLKSWAGEN AG" 295200881.996155 643297 8766324.5344162 15045459.3172073 598702589.898348 1154 . 295200881.996155 149069423.28763 165534030.491352 1 145 214
1 ""                             .      .               .                .                .    . .                .               .                . . 102   .
1 ""                             .      .               .                .                .    . .                .               .                . . 151   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . .  41   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 127   .
1 ""                             .      .               .                .                .    . .                .               .                . .  47   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 102   .
1 ""                             .      .               .                .                .    . .                .               .                . . 113   .
1 ""                             .      .               .                .                .    . .                .               .                . . 114   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 103   .
1 ""                             .      .               .                .                .    . .                .               .                . . 151   .
1 ""                             .      .               .                .                .    . .                .               .                . . 127   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 122   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . .  62   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . .  28   .
1 ""                             .      .               .                .                .    . .                .               .                . .   5   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . .   7   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . .   6   .
1 ""                             .      .               .                .                .    . .                .               .                . . 151   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 140   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . . 118   .
1 ""                             .      .               .                .                .    . .                .               .                . .  47   .
1 ""                             .      .               .                .                .    . .                .               .                . .  47   .
1 ""                             .      .               .                .                .    . .                .               .                . . 127   .
1 ""                             .      .               .                .                .    . .                .               .                . . 127   .
1 ""                             .      .               .                .                .    . .                .               .                . . 127   .
1 ""                             .      .               .                .                .    . .                .               .                . . 127   .
1 ""                             .      .               .                .                .    . .                .               .                . . 127   .
1 ""                             .      .               .                .                .    . .                .               .                . .   6   .
1 ""                             .      .               .                .                .    . .                .               .                . .   6   .
1 ""                             .      .               .                .                .    . .                .               .                . .  28   .
1 ""                             .      .               .                .                .    . .                .               .                . .   7   .
1 ""                             .      .               .                .                .    . .                .               .                . .  11   .
1 ""                             .      .               .                .                .    . .                .               .                . .  23   .
1 ""                             .      .               .                .                .    . .                .               .                . .  19   .
1 ""                             .      .               .                .                .    . .                .               .                . .  59   .
1 ""                             .      .               .                .                .    . .                .               .                . .  70   .
1 ""                             .      .               .                .                .    . .                .               .                . .  86   .
1 ""                             .      .               .                .                .    . .                .               .                . . 102   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . . 102   .
1 ""                             .      .               .                .                .    . .                .               .                . . 128   .
1 ""                             .      .               .                .                .    . .                .               .                . .  43   .
1 ""                             .      .               .                .                .    . .                .               .                . .   1   .
1 ""                             .      .               .                .                .    . .                .               .                . . 141   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .   6   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .
1 ""                             .      .               .                .                .    . .                .               .                . .  67   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .
1 ""                             .      .               .                .                .    . .                .               .                . .  19   .
1 ""                             .      .               .                .                .    . .                .               .                . .  47   .
1 ""                             .      .               .                .                .    . .                .               .                . .  23   .
1 ""                             .      .               .                .                .    . .                .               .                . .  28   .
1 ""                             .      .               .                .                .    . .                .               .                . .  28   .
1 ""                             .      .               .                .                .    . .                .               .                . .  28   .
1 ""                             .      .               .                .                .    . .                .               .                . .  47   .
1 ""                             .      .               .                .                .    . .                .               .                . .  47   .
1 ""                             .      .               .                .                .    . .                .               .                . .  47   .
1 ""                             .      .               .                .                .    . .                .               .                . .  23   .
1 ""                             .      .               .                .                .    . .                .               .                . .   1   .
end
label values country country
label def country 1 "DE", modify
label values subcountry subcountry
label def subcountry 1 "AE", modify
label def subcountry 5 "AR", modify
label def subcountry 6 "AT", modify
label def subcountry 7 "AU", modify
label def subcountry 11 "BE", modify
label def subcountry 19 "BR", modify
label def subcountry 23 "CA", modify
label def subcountry 28 "CN", modify
label def subcountry 41 "EE", modify
label def subcountry 43 "ES", modify
label def subcountry 46 "FR", modify
label def subcountry 47 "GB", modify
label def subcountry 59 "HU", modify
label def subcountry 62 "IL", modify
label def subcountry 67 "IT", modify
label def subcountry 70 "JP", modify
label def subcountry 86 "LU", modify
label def subcountry 102 "MX", modify
label def subcountry 103 "MY", modify
label def subcountry 113 "PE", modify
label def subcountry 114 "PH", modify
label def subcountry 118 "PT", modify
label def subcountry 122 "RS", modify
label def subcountry 127 "SE", modify
label def subcountry 128 "SG", modify
label def subcountry 140 "TR", modify
label def subcountry 141 "TW", modify
label def subcountry 145 "US", modify
label def subcountry 151 "ZA", modify
label values industrycode cpnycode
label def cpnycode 214 "2910", modify

Kind regards,

Marnik

[Clyde Schechter]

Well, I can get you part of the way there. The following code will create a new variable that tells you the total number of distinct subcountries mentioned in a company's data.

//  PRESERVE ORIGINAL SORT ORDER TO GO BACK TO
gen long obs_no = _n

by companynumber (subcountry), sort: gen distinct_countries = sum(subcountry != subcountry[_n-1])
by companynumber (subcountry): replace distinct_countries = _N

//  RESTORE ORIGINAL SORT ORDER
sort obs_no

I leave it there because I am not sure what you mean by "foreign." It might mean countries other than your own country (whatever that may be). Or it might mean different from the one mentioned in the country variable of the first observation for the company. Or maybe even something different: is Wales a different country from England, or are they both the United Kingdom? So I'll leave it to you to sort out the meaning of "foreign" and adjust the results accordingly.

[Marnik Gun]

Thank you for your reply! You're right I wasn't being very clear with what I meant with foreign.
For this assignment we selected a sample of German manufacturing companies, meaning that subsidiaries cannot be based in Germany.
However, I found out that I don't need the German subsidiaries for another variable, so I could just drop them and that problem is solved.

I ran your code and it seems that instead of creating distinct countries, the code created a variable counting the number of subsidiaries per firm.
Since 903 distinct countries seems out of place, but 903 different subsidiaries is very plausible.
Which is great, since I also need those subsidiaries numbers.
So I was wondering if you would be so kind to help once more with the distinct number of countries a firm(in this data example Volkswagen) has subsidiaries in,

* Example generated by -dataex-. For more info, type help dataex
clear
input int companynumber str101 company double revenue long employees double(rd netincome totalassets) int subsidiaries double(export sales debt equity) long(country subcountry industrycode obs_no) float distinct_countries
1 "VOLKSWAGEN AG" 295200881.996155 643297 8766324.5344162 15045459.3172073 598702589.898348 1154 . 295200881.996155 149069423.28763 165534030.491352 1 145 214   1 903
1 ""                             .      .               .                .                .    . .                .               .                . . 102   .   2 903
1 ""                             .      .               .                .                .    . .                .               .                . . 151   .   3 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .   4 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .   5 903
1 ""                             .      .               .                .                .    . .                .               .                . .  41   .   6 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .   7 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .   8 903
1 ""                             .      .               .                .                .    . .                .               .                . . 127   .   9 903
1 ""                             .      .               .                .                .    . .                .               .                . .  47   .  10 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  11 903
1 ""                             .      .               .                .                .    . .                .               .                . . 102   .  12 903
1 ""                             .      .               .                .                .    . .                .               .                . . 113   .  13 903
1 ""                             .      .               .                .                .    . .                .               .                . . 114   .  14 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  15 903
1 ""                             .      .               .                .                .    . .                .               .                . . 103   .  16 903
1 ""                             .      .               .                .                .    . .                .               .                . . 151   .  17 903
1 ""                             .      .               .                .                .    . .                .               .                . . 127   .  18 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  19 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  20 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  21 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  22 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  23 903
1 ""                             .      .               .                .                .    . .                .               .                . . 122   .  24 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  25 903
1 ""                             .      .               .                .                .    . .                .               .                . .  62   .  26 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  27 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  28 903
1 ""                             .      .               .                .                .    . .                .               .                . .  46   .  29 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  30 903
1 ""                             .      .               .                .                .    . .                .               .                . .  28   .  31 903
1 ""                             .      .               .                .                .    . .                .               .                . .   5   .  32 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  33 903
1 ""                             .      .               .                .                .    . .                .               .                . .   7   .  34 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  35 903
1 ""                             .      .               .                .                .    . .                .               .                . .   6   .  36 903
1 ""                             .      .               .                .                .    . .                .               .                . . 151   .  37 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  38 903
1 ""                             .      .               .                .                .    . .                .               .                . . 140   .  39 903
1 ""                             .      .               .                .                .    . .                .               .                . . 145   .  40 903

end
label values country country
label def country 1 "DE", modify
label values subcountry subcountry
label def subcountry 1 "AE", modify
label def subcountry 5 "AR", modify
label def subcountry 6 "AT", modify
label def subcountry 7 "AU", modify
label def subcountry 11 "BE", modify
label def subcountry 19 "BR", modify
label def subcountry 23 "CA", modify
label def subcountry 28 "CN", modify
label def subcountry 41 "EE", modify
label def subcountry 43 "ES", modify
label def subcountry 46 "FR", modify
label def subcountry 47 "GB", modify
label def subcountry 59 "HU", modify
label def subcountry 62 "IL", modify
label def subcountry 67 "IT", modify
label def subcountry 70 "JP", modify
label def subcountry 86 "LU", modify
label def subcountry 102 "MX", modify
label def subcountry 103 "MY", modify
label def subcountry 113 "PE", modify
label def subcountry 114 "PH", modify
label def subcountry 118 "PT", modify
label def subcountry 122 "RS", modify
label def subcountry 127 "SE", modify
label def subcountry 128 "SG", modify
label def subcountry 140 "TR", modify
label def subcountry 141 "TW", modify
label def subcountry 145 "US", modify
label def subcountry 151 "ZA", modify
label values industrycode cpnycode
label def cpnycode 214 "2910", modify

Kind regards,

Marnik

[Clyde Schechter]

Sorry, you are right. Here is correct code (leaving out the preserving and restoring of the original sort order):

by companynumber (subcountry), sort: gen distinct_countries = sum(subcountry != subcountry[_n-1])
by companynumber (subcountry): replace distinct_countries = distinct_countries[_N]
by companynumber: gen distinct_subsidiaries = _N

[Marnik Gun]

Thanks, the code worked!