Replacing missing values with already existing match

[Nick Baradar]

Hello,

I have a data with two variables - company name, and gvkey. Each company name has a unique gvkey. There are several observations with the company name where their gvkey is missing, but their gvkey is available in earlier or later observations. I would like to know of there is a way that I can fill those missing observation in Stata: Thank you very much in advance!

* Example generated by -dataex-. For more info, type help dataex
clear
input str450 parentcompany long gvkey
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"  14898
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"  14898
"3D Systems Corporation (NYSE:DDD)"  14898
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3D Systems Corporation (NYSE:DDD)"      .
"3M Company (NYSE:MMM)"                  .
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"                  .
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"3M Company (NYSE:MMM)"               7435
"AB Volvo (publ) (OM:VOLV B)"            .
"AB Volvo (publ) (OM:VOLV B)"            .
"AB Volvo (publ) (OM:VOLV B)"            .
"AB Volvo (publ) (OM:VOLV B)"            .
"AB Volvo (publ) (OM:VOLV B)"            .
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"            .
"AB Volvo (publ) (OM:VOLV B)"            .
"AB Volvo (publ) (OM:VOLV B)"        11217
"AB Volvo (publ) (OM:VOLV B)"        11217
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                  147988
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                       .
"AECOM (NYSE:ACM)"                  147988
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."   6347
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AECOM Energy & Construction, Inc."      .
"AGC Networks Limited (BSE:500463)"      .
"AGC Networks Limited (BSE:500463)"      .
"AGC Networks Limited (BSE:500463)"      .
"AGC Networks Limited (BSE:500463)"      .
"AGC Networks Limited (BSE:500463)"      .
end

[Nick Cox]

https://www.statalist.org/forums/for...-interpolation introduced the mipolate command on SSC. Its groupwise option applies here.

bysort parentcompany (gvkey) : replace gvkey = gvkey[_n-1] if missing(gvkey)

will do the same if and only if there is no conflict of non-missing values.

[Nick Baradar]

Hello Nick, thank you very much for your help, it works fine!