filling blank cell in panel dataset

[Zariab Hossain]

Dear Stata users

I have the following panel:

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str15 kom_name int year float(no_close no_open no_office)
"Ale"       2014 . . 1
"Ale"       2015 . . .
"Ale"       2016 . . .
"Ale"       2017 . . .
"Ale"       2018 . . .
"Ale"       2019 1 . 1
"Ale"       2020 . . .
"Ale"       2021 . . .
"Ale"       2022 . . .
"Ale"       2023 . . 0
"Alingsås" 2014 . . 1
"Alingsås" 2015 . . .
"Alingsås" 2016 . . .
"Alingsås" 2017 . . .
"Alingsås" 2018 . . .
"Alingsås" 2019 . . 1
"Alingsås" 2020 . . .
"Alingsås" 2021 . . .
"Alingsås" 2022 . . .
"Alingsås" 2023 . . 1
"Alvesta"   2014 . . 1
"Alvesta"   2015 . . .
"Alvesta"   2016 . . .
"Alvesta"   2017 . . .
"Alvesta"   2018 . . .
"Alvesta"   2019 . . 1
"Alvesta"   2020 1 . .
"Alvesta"   2021 . . .
"Alvesta"   2022 . . .
"Alvesta"   2023 . . 0
"Aneby"     2014 . . 1
"Aneby"     2015 . . .
"Aneby"     2016 . . .
"Aneby"     2017 . . .
"Aneby"     2018 . . .
"Aneby"     2019 1 . 1
"Aneby"     2020 . . .
"Aneby"     2021 . . .
"Aneby"     2022 . . .
"Aneby"     2023 . . 0
end

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

I have data for year 2014, 2019 and 2023 for no_office var for all kom_name. I want to impute the rest of the info for no_office variable with this formula: no_office=no_office[_n-1]+no_open-no_close. I am not sure how to do this. Any help would be highly appreciated.

I tried the following:

foreach i of numlist 2015/2018 2020/2022{
bys kom_name: replace no_office=no_office[_n-1] if missing(no_open) & missing(no_close) & year==`i'
}

But it captures part of the id that had missing no_open and no_close (the trivial one).

Zariab Hossain
Uppsala University

[Clyde Schechter]

I'm sorry, but I can't make any sense of your code in the context of your example data, and I don't understand from your explanation what you want to do. Please post back showing what you want the results for your example data to look like.

[Zariab Hossain]

Thanks Clyde for your time to see my query. I have solved it.