generate variables with conditions

[Constanze Losert]

Hello everyone,

I am working with ownership data from S&P1500 firms from 2017-2022 where one firm is mentioned in many rows because of all diferent investors per year per firm. I generated several variables per year per firm of S&P1500. in the end i collapse all these generated variables to get only one variable value per firm per year. one of the generated variable is investor_blockholder by the following command (to get only the investor holding % of the biggest institutional investor per year per firm) which worked out:

// identify highest ownership per firm per year of one institutional investor
bysort instrument year: egen investor_blockholder = max(holdingspctoftradedshareshe) if investortypedescription != "Individual Investor" & investortypedescription != "Research Firm"

Now i want to generate a variable which shows the investor country of the investor_blockholder per firm per year. I tried the following command which did not work:

bysort instrument year: gen blockholder_country = investoraddresscountry if investor_blockholder == max(holdingspctoftradedshareshe) & investortypedescription != "Individual Investor" & investortypedescription != "Research Firm"


It seems like stata does not recognize when investor_blockholder == holdingspctoftradedshareshe. I also tried another command where no values changed after the replace command but there should havs been one change per firm per year

gen blockholder_country = 0
replace blockholder_country = 1 if investor_blockholder == holdingspctoftradedshareshe

How do i correct the command to generate the variable which shows only the investor country for the investor which equals the blockholder investor (biggest holding % (holdingspctoftradedshareshe))?

The following shows a little excerpt from the ownership data with the relevant variables:

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 instrument float year str36 investoraddresscountry float investor_blockholder double holdingspctoftradedshareshe str29 investortypedescription
"US0003602069" 2017 "Australia"      9.144  .0004 "Investment Advisor"           
"US0003602069" 2017 "Australia"      9.144  .0138 "Investment Advisor"           
"US0003602069" 2017 "Canada"         9.144   .061 "Investment Advisor"           
"US0003602069" 2017 "Canada"         9.144  .0002 "Investment Advisor"           
"US0003602069" 2017 "Canada"         9.144  .0477 "Pension Fund"                 
"US0003602069" 2017 "Canada"         9.144  .0021 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "Canada"         9.144  .0001 "Investment Advisor"           
"US0003602069" 2017 "Canada"         9.144  .0316 "Investment Advisor"           
"US0003602069" 2017 "Canada"         9.144  .0065 "Investment Advisor"           
"US0003602069" 2017 "Canada"         9.144  .1025 "Investment Advisor"           
"US0003602069" 2017 "Canada"             .  .0004 "Research Firm"                
"US0003602069" 2017 "France"         9.144  .0054 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "Germany"        9.144  .1769 "Investment Advisor"           
"US0003602069" 2017 "Ireland"        9.144  .0212 "Investment Advisor"           
"US0003602069" 2017 "Ireland"        9.144  .1121 "Investment Advisor"           
"US0003602069" 2017 "Ireland"        9.144  .0939 "Investment Advisor"           
"US0003602069" 2017 "Netherlands"    9.144  .0117 "Investment Advisor"           
"US0003602069" 2017 "Norway"         9.144  .2613 "Sovereign Wealth Fund"        
"US0003602069" 2017 "Spain"          9.144  .0047 "Investment Advisor"           
"US0003602069" 2017 "Sweden"         9.144  .0115 "Investment Advisor"           
"US0003602069" 2017 "Switzerland"    9.144  .0044 "Bank and Trust"               
"US0003602069" 2017 "Switzerland"    9.144  .0143 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "Switzerland"    9.144  .0239 "Investment Advisor"           
"US0003602069" 2017 "Switzerland"    9.144  .1394 "Bank and Trust"               
"US0003602069" 2017 "Switzerland"    9.144  .0369 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United Kingdom" 9.144 1.2514 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United Kingdom" 9.144  .0001 "Investment Advisor"           
"US0003602069" 2017 "United Kingdom" 9.144  .0212 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United Kingdom" 9.144  .0144 "Investment Advisor"           
"US0003602069" 2017 "United Kingdom" 9.144  .1576 "Investment Advisor"           
"US0003602069" 2017 "United Kingdom" 9.144  .0026 "Investment Advisor"           
"US0003602069" 2017 "United Kingdom" 9.144  .0125 "Investment Advisor"           
"US0003602069" 2017 "United Kingdom" 9.144  .0205 "Investment Advisor"           
"US0003602069" 2017 "United Kingdom" 9.144  .0212 "Investment Advisor"           
"US0003602069" 2017 "United Kingdom" 9.144  .0233 "Investment Advisor"           
"US0003602069" 2017 "United Kingdom" 9.144  .1572 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United Kingdom" 9.144  .0451 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .1113 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0139 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0123 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .2363 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0004 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0067 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0667 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .3678 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0353 "Hedge Fund"                   
"US0003602069" 2017 "United States"  9.144  .0358 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .5395 "Hedge Fund"                   
"US0003602069" 2017 "United States"  9.144   .055 "Bank and Trust"               
"US0003602069" 2017 "United States"  9.144  .0371 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .0158 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .0021 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .0461 "Bank and Trust"               
"US0003602069" 2017 "United States"  9.144  .0811 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144   .052 "Sovereign Wealth Fund"        
"US0003602069" 2017 "United States"  9.144  .0281 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144 6.0076 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .6596 "Bank and Trust"               
"US0003602069" 2017 "United States"  9.144  .9894 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144   .031 "Pension Fund"                 
"US0003602069" 2017 "United States"  9.144   .029 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0002 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0154 "Pension Fund"                 
"US0003602069" 2017 "United States"  9.144  .1944 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .0792 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144   .152 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144   .212 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0043 "Bank and Trust"               
"US0003602069" 2017 "United States"  9.144  .0246 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0008 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .0618 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .0571 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144 1.1557 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .0179 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0112 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144    .02 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144   .021 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .1893 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0331 "Pension Fund"                 
"US0003602069" 2017 "United States"  9.144  .0044 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .0021 "Insurance Company"            
"US0003602069" 2017 "United States"  9.144  9.144 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0045 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144 3.6928 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144   .017 "Bank and Trust"               
"US0003602069" 2017 "United States"  9.144  .0169 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .1245 "Pension Fund"                 
"US0003602069" 2017 "United States"  9.144  .0143 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0013 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0098 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0396 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .4542 "Pension Fund"                 
"US0003602069" 2017 "United States"  9.144  .0495 "Investment Advisor/Hedge Fund"
"US0003602069" 2017 "United States"  9.144  .0064 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0001 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144   .128 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0111 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .8963 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144 1.7528 "Investment Advisor"           
"US0003602069" 2017 "United States"  9.144  .0032 "Investment Advisor"           
end

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


Thank you in advance and best regards
Constanze Losert

[Clyde Schechter]

by instrument year (holdingspctoftradedshareshe), sort: ///
    gen blockholder_country = investoraddresscountry[_N]

But, there is an underlying problem with your blockholder_country construct: what happens when there are multiple investors from different countries who are tied for the maximum holdings? Calculating the amount of those maximum holdings itself is not problematic, but for the country you have a problem. The above code will break the tie for blockholder_country at random and irreproducibly. You need either to be certain that no such ties will ever occur in the data, or you need a rule for how to break the ties.

Added: This code also assumes that the holdings variable never has a missing value. It will give incorrect results if this assumption fails.

[Constanze Losert]

thank you very much for responding! I think there is one thing missing if I use the code like this. I generated investor_blockholder only looking at institutional investor and I want to get the country of the biggest institutional investor as well. Now I get the country of the biggest investor in general which are also individual investors in some cases. If I add the condition, there is no error but stata does not recognize the condition and still give out the biggest investor in general by the following code:

by instrument year (holdingspctoftradedshareshe), sort: ///
gen blockholder_country = investoraddresscountry[_N] if investortypedescription != "Individual Investor" & investortypedescription != "Research Firm"

[Constanze Losert]

solved it, thank you very much!