replacing missing values with a specific year observation

[Giovanni Coppola]

Hello everyone, I need some help in composing a code for replacing missing values with the value of a specific year-observation. I have data structured in the following way:

id company year dum1
1 comp1 2013 1
1 comp1 2014 .
1 comp1 2015 .
1 comp1 2016 .
1 comp1 2017 .
1 comp1 2018 .
1 comp1 2019 .
2 comp2 2013 0
2 comp2 2014 .
2 comp2 2015 .
2 comp2 2016 .
2 comp2 2017 .
2 comp2 2018 .
2 comp2 2019 .
3 comp3 2013 .
3 comp3 2014 .
3 comp3 2015 .
3 comp3 2016 .
3 comp3 2017 .
3 comp3 2018 .
3 comp3 2019 .


I would like to replace missings in variable dum1 with the values of year 2013 observations so that for company 1 all subsequent years-obs. will have 1 as value, for company 2 will have 0 as value, for company 3 will have . as value.

How can I do it?

Many thanks!

[Joro Kolev]

Try

bysort id (year): replace dum1 = dum1[1]

and if it does not work, use -dataex- to present the example of your data.

[Giovanni Coppola]

Hi Joro, the code doesn,'t work. Here is the dataex:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str182 Ragionesociale int Year str8 Paese float dim
"ZUMTOBEL GROUP AG"                                           2013 "Austria" 1
"ZUMTOBEL GROUP AG"                                           2014 "Austria" .
"ZUMTOBEL GROUP AG"                                           2015 "Austria" .
"ZUMTOBEL GROUP AG"                                           2016 "Austria" .
"ZUMTOBEL GROUP AG"                                           2017 "Austria" .
"ZUMTOBEL GROUP AG"                                           2018 "Austria" .
"ZUMTOBEL GROUP AG"                                           2019 "Austria" .
"STUERMSFS GMBH"                                              2013 "Austria" 1
"STUERMSFS GMBH"                                              2014 "Austria" .
"STUERMSFS GMBH"                                              2015 "Austria" .
"STUERMSFS GMBH"                                              2016 "Austria" .
"STUERMSFS GMBH"                                              2017 "Austria" .
"STUERMSFS GMBH"                                              2018 "Austria" .
"STUERMSFS GMBH"                                              2019 "Austria" .
"WOLFORD AG"                                                  2013 "Austria" 1
"WOLFORD AG"                                                  2014 "Austria" .
"WOLFORD AG"                                                  2015 "Austria" .
"WOLFORD AG"                                                  2016 "Austria" .
"WOLFORD AG"                                                  2017 "Austria" .
"WOLFORD AG"                                                  2018 "Austria" .
"WOLFORD AG"                                                  2019 "Austria" .
"MARKUS STOLZ, GESELLSCHAFT M.B.H. & CO. KG., INSTALLATIONEN" 2013 "Austria" 1
"MARKUS STOLZ, GESELLSCHAFT M.B.H. & CO. KG., INSTALLATIONEN" 2014 "Austria" .
"MARKUS STOLZ, GESELLSCHAFT M.B.H. & CO. KG., INSTALLATIONEN" 2015 "Austria" .
"MARKUS STOLZ, GESELLSCHAFT M.B.H. & CO. KG., INSTALLATIONEN" 2016 "Austria" .
"MARKUS STOLZ, GESELLSCHAFT M.B.H. & CO. KG., INSTALLATIONEN" 2017 "Austria" .
"MARKUS STOLZ, GESELLSCHAFT M.B.H. & CO. KG., INSTALLATIONEN" 2018 "Austria" .
"MARKUS STOLZ, GESELLSCHAFT M.B.H. & CO. KG., INSTALLATIONEN" 2019 "Austria" .
"EHG STAHLZENTRUM GMBH & CO OG"                               2013 "Austria" 1
"EHG STAHLZENTRUM GMBH & CO OG"                               2014 "Austria" .
"EHG STAHLZENTRUM GMBH & CO OG"                               2015 "Austria" .
"EHG STAHLZENTRUM GMBH & CO OG"                               2016 "Austria" .
"EHG STAHLZENTRUM GMBH & CO OG"                               2017 "Austria" .
"EHG STAHLZENTRUM GMBH & CO OG"                               2018 "Austria" .
"EHG STAHLZENTRUM GMBH & CO OG"                               2019 "Austria" .
"GANAHL AKTIENGESELLSCHAFT"                                   2013 "Austria" 1
"GANAHL AKTIENGESELLSCHAFT"                                   2014 "Austria" .
"GANAHL AKTIENGESELLSCHAFT"                                   2015 "Austria" .
"GANAHL AKTIENGESELLSCHAFT"                                   2016 "Austria" .
"GANAHL AKTIENGESELLSCHAFT"                                   2017 "Austria" .
"GANAHL AKTIENGESELLSCHAFT"                                   2018 "Austria" .
"GANAHL AKTIENGESELLSCHAFT"                                   2019 "Austria" .
"HEFEL REALVERMOEGEN AG"                                      2013 "Austria" 0
"HEFEL REALVERMOEGEN AG"                                      2014 "Austria" .
"HEFEL REALVERMOEGEN AG"                                      2015 "Austria" .
"HEFEL REALVERMOEGEN AG"                                      2016 "Austria" .
"HEFEL REALVERMOEGEN AG"                                      2017 "Austria" .
"HEFEL REALVERMOEGEN AG"                                      2018 "Austria" .
"HEFEL REALVERMOEGEN AG"                                      2019 "Austria" .
"DOPPELMAYR SEILBAHNEN GMBH"                                  2013 "Austria" 1
"DOPPELMAYR SEILBAHNEN GMBH"                                  2014 "Austria" .
"DOPPELMAYR SEILBAHNEN GMBH"                                  2015 "Austria" .
"DOPPELMAYR SEILBAHNEN GMBH"                                  2016 "Austria" .
"DOPPELMAYR SEILBAHNEN GMBH"                                  2017 "Austria" .
"DOPPELMAYR SEILBAHNEN GMBH"                                  2018 "Austria" .
"DOPPELMAYR SEILBAHNEN GMBH"                                  2019 "Austria" .
"COLLINI HOLDING AG"                                          2013 "Austria" 1
"COLLINI HOLDING AG"                                          2014 "Austria" .
"COLLINI HOLDING AG"                                          2015 "Austria" .
"COLLINI HOLDING AG"                                          2016 "Austria" .
"COLLINI HOLDING AG"                                          2017 "Austria" .
"COLLINI HOLDING AG"                                          2018 "Austria" .
"COLLINI HOLDING AG"                                          2019 "Austria" .
"EMMI OESTERREICH GMBH"                                       2013 "Austria" 1
"EMMI OESTERREICH GMBH"                                       2014 "Austria" .
"EMMI OESTERREICH GMBH"                                       2015 "Austria" .
"EMMI OESTERREICH GMBH"                                       2016 "Austria" .
"EMMI OESTERREICH GMBH"                                       2017 "Austria" .
"EMMI OESTERREICH GMBH"                                       2018 "Austria" .
"EMMI OESTERREICH GMBH"                                       2019 "Austria" .
"AUTOLAND FINK GMBH"                                          2013 "Austria" 1
"AUTOLAND FINK GMBH"                                          2014 "Austria" .
"AUTOLAND FINK GMBH"                                          2015 "Austria" .
"AUTOLAND FINK GMBH"                                          2016 "Austria" .
"AUTOLAND FINK GMBH"                                          2017 "Austria" .
"AUTOLAND FINK GMBH"                                          2018 "Austria" .
"AUTOLAND FINK GMBH"                                          2019 "Austria" .
"DOPPELMAYR HOLDING SE"                                       2013 "Austria" 1
"DOPPELMAYR HOLDING SE"                                       2014 "Austria" .
"DOPPELMAYR HOLDING SE"                                       2015 "Austria" .
"DOPPELMAYR HOLDING SE"                                       2016 "Austria" .
"DOPPELMAYR HOLDING SE"                                       2017 "Austria" .
"DOPPELMAYR HOLDING SE"                                       2018 "Austria" .
"DOPPELMAYR HOLDING SE"                                       2019 "Austria" .
"DOPPELMAYR TRANSPORT TECHNOLOGY GMBH"                        2014 "Austria" .
"DOPPELMAYR TRANSPORT TECHNOLOGY GMBH"                        2015 "Austria" .
"DOPPELMAYR TRANSPORT TECHNOLOGY GMBH"                        2016 "Austria" .
"DOPPELMAYR TRANSPORT TECHNOLOGY GMBH"                        2017 "Austria" .
"DOPPELMAYR TRANSPORT TECHNOLOGY GMBH"                        2018 "Austria" .
"DOPPELMAYR TRANSPORT TECHNOLOGY GMBH"                        2019 "Austria" .
"ZUMTOBEL LIGHTING GMBH"                                      2013 "Austria" 1
"ZUMTOBEL LIGHTING GMBH"                                      2014 "Austria" .
"ZUMTOBEL LIGHTING GMBH"                                      2015 "Austria" .
"ZUMTOBEL LIGHTING GMBH"                                      2016 "Austria" .
"ZUMTOBEL LIGHTING GMBH"                                      2017 "Austria" .
"ZUMTOBEL LIGHTING GMBH"                                      2018 "Austria" .
"ZUMTOBEL LIGHTING GMBH"                                      2019 "Austria" .
"SKILIFTE LECH ING. BILDSTEIN GESELLSCHAFT M.B.H."            2013 "Austria" 1
"SKILIFTE LECH ING. BILDSTEIN GESELLSCHAFT M.B.H."            2014 "Austria" .
"SKILIFTE LECH ING. BILDSTEIN GESELLSCHAFT M.B.H."            2015 "Austria" .
end

[Raymond Zhang]

bysort Ragionesociale (Year): replace dim = dim[1]

[Joro Kolev]

Giovanni, another useful strategy around here is not to say "the code doesn,'t work" but to explain how exactly the code fails... So that we know which problem we are trying to solve.

For the data that you presented with -dataex- the code works, with the qualification that you have one company for which the first year in sample is not 2013, but rather 2014. In this case the code assigns the value for year 2014 for all other years, which might, or might not be what you want.

How about this code:

. gen dim2013temp = dim if Year==2013
(86 missing values generated)

. egen dim2013 = mean(dim2013temp), by( Ragionesociale)
(6 missing values generated)

does it do what you want?

[Giovanni Coppola]

Many thanks to all, I was able to do it.