How do I fill in missing values using the highest/last observation-year?

[Anna Bee]

Hello everyone,

I would like to replace the missing values (leftceo) by the last given observation-year, to calculate the tenure of a CEO.
This means in my case that the three missing values should be replaced by "2020".

I tried the following command: "bysort gvkey_year: replace leftceo=max(YEAR) if missing(leftceo)". Unfortunately Stata states that it is an invalid syntax.

Could someone help me, please?

Thanks in advance!

[Øyvind Snilsberg]

bysort gvkey becameceo (year): replace leftceo = year[_N] if mi(leftceo)

... but when becameceo is 1996 leftceo is the last observation-year + 1

[Anna Bee]

Hello Øyvind,

thank you for your quick response.

Unfortunately, this command does not replace all the missing values with "2020" but with the year from the respective column (see picture below).
Do you have an idea how to change it so that all the missing values are replaced with the last observation year (in this case 2020)?

Referring to "but when becameceo is 1996 leftceo is the last observation-year + 1" I must add that the 2018 is the actual year the CEO left (I did not created this value - it is the original value from the database). I just need to replace the missing values.

Hope it's clear

Thanks in advance!

[Nick Cox]

Oyvind Snilsberg's code looks good to me. I can't reproduce #3.

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 gvkey float(year becameceo leftceo)
"000104" 2018 2018 .
"000104" 2019 2018 .
"000104" 2020 2018 .
end

bysort gvkey becameceo (year) : replace leftceo = year[_N]

. list 

     +------------------------------------+
     |  gvkey   year   became~o   leftceo |
     |------------------------------------|
  1. | 000104   2018       2018      2020 |
  2. | 000104   2019       2018      2020 |
  3. | 000104   2020       2018      2020 |
     +------------------------------------+

.