fill rows with same identifier

[Jana Schue]

Hey everybody,

In my dataset are rows with missing values and I want to fill them with values in respect to the same identifiers.
My identifiers are "companyid" & "boardid".

In red, you see examples which state the issue.

I want to drop rows with missing values but not those marked red.

* Example generated by -dataex-. For more info, type help dataex
clear
input long(companyid boardid) str8(startcompanydatestartrole startcompanydateendrole) str95 role
  18814   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
  36362   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
 378781   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
 378806   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
 378811   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
 378816   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
 378831   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
 378836   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
 378846   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
1013205   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
1642262   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
2004801   10925 "20110104" "20160119" "Independent Director (Brd) (SD)"  
  15486  625180 ""         ""         "Chairman"                         
  15486  625180 ""         ""         "Chairman"                         
  15486  625180 ""         ""         "Trustee"                          
  15486  625180 ""         ""         "Chairman"                         
  15486  625180 ""         ""         "Trustee"                          
  15486  625180 ""         ""         "Chairman"                         
  15486  625180 ""         ""         "Trustee"                          
  21616  381972 "20040201" "20070101" "Senior Advisor (Non-Brd)"         
  39646  625180 "N"        "C"        "Chairman Emeritus (Non-Brd)"      
  39646 1378224 "20180125" "C"        "Advisory Council Member (Non-Brd)"
  39646 1878540 ""         ""         "Director -  SD (Brd) (SD)"        
  39646 1899836 "20100101" "20190207" "Chairman (Brd) (SD)"              
  39646 1947958 "20160101" "C"        "Advisor (Non-Brd)"                
  39646 2992276 ""         ""         "Director -  SD (Brd) (SD)"        
  39646 2992276 "20190207" "20190904" "Chairman (Brd) (SD)"              
  41400  625180 "N"        "C"        "Chairman Emeritus (Non-Brd)"      
  41400 1378224 "20180125" "C"        "Advisory Council Member (Non-Brd)"
  41400 1878540 ""         ""         "Director -  SD (Brd) (SD)"        
  41400 1899836 "20100101" "20190207" "Chairman (Brd) (SD)"              
  41400 1947958 "20160101" "C"        "Advisor (Non-Brd)"                
  41400 2992276 ""         ""         "Director -  SD (Brd) (SD)"        
  41400 2992276 "20190207" "20190904" "Chairman (Brd) (SD)"              
  41925 1623243 "20100203" "20140603" "Independent Director (Brd) (SD)"  
  41925 1878540 ""         ""         "Director -  SD (Brd) (SD)"        
  41925 1899836 "20100101" "20190207" "Chairman (Brd) (SD)"              
  41925 1947958 "20160101" "C"        "Advisor (Non-Brd)"                
  52869  625180 "N"        "C"        "Chairman Emeritus (Non-Brd)"      
  52869 1378224 "20180125" "C"        "Advisory Council Member (Non-Brd)"
  52869 1623243 "20100203" "20140603" "Independent Director (Brd) (SD)"  
  52869 1878540 ""         ""         "Director -  SD (Brd) (SD)"        
  52869 1878540 ""         ""         "Director -  SD (Brd) (SD)"        
  52869 1899836 "20100101" "20190207" "Chairman (Brd) (SD)"              
  52869 1899836 "20100101" "20190207" "Chairman (Brd) (SD)"              
  52869 1947958 "20160101" "C"        "Advisor (Non-Brd)"                
  52869 1947958 "20160101" "C"        "Advisor (Non-Brd)"                
  52869 2992276 ""         ""         "Director -  SD (Brd) (SD)"        
  52869 2992276 "20190207" "20190904" "Chairman (Brd) (SD)"              
  54449   15486 "19830101" "19931001" "Chairman/President/CEO (Brd) (ED)"
  54449   15486 "19931001" "19990218" "Chairman/CEO (Brd) (ED)"          
  54449   15486 "19990722" "20020124" "Chairman/President/CEO (Brd) (ED)"
  54449  625180 ""         ""         "Trustee"                          
  63013   15486 ""         ""         ""                                 
 101312   21616 ""         ""         "Director -  SD (Brd) (SD)"        
 101312  381972 "20040201" "20070101" "Senior Advisor (Non-Brd)"         
 101312  626943 "20070301" "20070401" "Chairman (Executive) (Brd) (ED)"  
 101312  626943 "20070301" "20070401" "Chairman (Brd) (ED)"              
 101312  626943 "20070401" "20080101" "CEO (Brd) (ED)"                   
 123932  625180 "N"        "C"        "Chairman Emeritus (Non-Brd)"      
 123932 1378224 "20180125" "C"        "Advisory Council Member (Non-Brd)"
 123932 1878540 ""         ""         "Director -  SD (Brd) (SD)"        
 123932 1899836 "20100101" "20190207" "Chairman (Brd) (SD)"              
 123932 1947958 "20160101" "C"        "Advisor (Non-Brd)"                
 123932 2992276 ""         ""         "Director -  SD (Brd) (SD)"        
 123932 2992276 "20190207" "20190904" "Chairman (Brd) (SD)"              
 135607   15486 "19931001" "19990218" "Chairman/CEO (Brd) (ED)"          
 135607  625180 ""         ""         "Trustee"                          
 381972   21616 ""         ""         "Director -  SD (Brd) (SD)"        
 381972  626943 "20070301" "20070401" "Chairman (Brd) (ED)"              
 381972  626943 "20070401" "20080101" "CEO (Brd) (ED)"                   
 620934  625180 "N"        "C"        "Chairman Emeritus (Non-Brd)"      
 620934 1378224 "20180125" "C"        "Advisory Council Member (Non-Brd)"
 620934 1878540 ""         ""         "Director -  SD (Brd) (SD)"        
 620934 1899836 "20100101" "20190207" "Chairman (Brd) (SD)"              
 620934 1947958 "20160101" "C"        "Advisor (Non-Brd)"                
 620934 2992276 ""         ""         "Director -  SD (Brd) (SD)"        
 620934 2992276 "20190207" "20190904" "Chairman (Brd) (SD)"              
 625180   15486 ""         ""         "Chairman (Executive) (Brd) (ED)"  
 625180   15486 "19830101" "19931001" "Chairman/President/CEO (Brd) (ED)"
 625180   15486 "19830101" "19931001" "Chairman/President/CEO (Brd) (ED)"
 625180   15486 "19931001" "19990218" "Chairman/CEO (Brd) (ED)"          
 625180   15486 "19931001" "19990218" "Chairman/CEO (Brd) (ED)"          
 625180   15486 "19990722" "20020124" "Chairman/President/CEO (Brd) (ED)"
 625180   15486 "19990722" "20020124" "Chairman/President/CEO (Brd) (ED)"
 625180 1378224 "20180125" "C"        "Advisory Council Member (Non-Brd)"
 625180 1878540 ""         ""         "Director -  SD (Brd) (SD)"        
 625180 1899836 "20100101" "20190207" "Chairman (Brd) (SD)"              
 625180 1947958 "20160101" "C"        "Advisor (Non-Brd)"                
 625180 2992276 ""         ""         "Director -  SD (Brd) (SD)"        
 625180 2992276 "20190207" "20190904" "Chairman (Brd) (SD)"              
 626943  381972 "20040201" "20070101" "Senior Advisor (Non-Brd)"         
 626943  381972 "20040201" "20070101" "Senior Advisor (Non-Brd)"         
 854251   21616 ""         ""         "Director -  SD (Brd) (SD)"        
 854251  381972 "20040201" "20070101" "Senior Advisor (Non-Brd)"         
 854251  626943 "20070301" "20070401" "Chairman (Brd) (ED)"              
 854251  626943 "20070401" "20080101" "CEO (Brd) (ED)"                   
1214855 1623243 "20100203" "20140603" "Independent Director (Brd) (SD)"  
1214855 1899836 "20100101" "20190207" "Chairman (Brd) (SD)"              
1217664   21616 ""         ""         "Director -  SD (Brd) (SD)"        
end

Thank you!

Kind regards
Jana

[Nick Cox]

This is an FAQ. Please see https://www.stata.com/support/faqs/d...issing-values/

[Jana Schue]

Nick:
Thank you for the hint!

I used:

Command:

gsort directorid companyid boardid 
quietly by directorid companyid boardid: replace startcompanydatestartrole_year[_n-1] if startcompanydatestartrole==""

but getting an error:

weights not allowed

Can someone please help me with the error in this case?

Thank you
Kind regards,
Jana

[Nick Cox]

replace startcompanydatestartrole_year[_n-1]

makes no sense to Stata -- or to any Stata user or programmer knowing Stata syntax. Look at the help for replace which explains that the basic pattern is replace oldvar = exp That pattern may be complicated by other syntax, but it is essential as the core. Your syntax fails as

startcompanydatestartrole_year[_n-1]

is not the name of an existing variable: the element [_n-1] stops it being a simple variable name. That is what the error message is about: the square brackets are interpreted as an attempt to specify weights. The error message is wrong insofar as you aren't trying to specify weights, but no program is perfect. Stata didn't get that far, but the replace statement is also wrong because there is no complement = exp. It is a bit of a surprise that Stata didn't flag that in your dataset startcompanydatestartrole_year isn't (according to #1) a variable name at all. This may be closer to what you want.

bysort directorid companyid boardid (year): replace startcompanydatestartrole =  startcompanydatestartrole[_n-1] if startcompanydatestartrole==""

Your code seems to show that you're guessing what Stata syntax might work. This strategy has low probability of success. There is no substitute for following the documentation with total attention to detail....