Replace missing values by (copying) non-missing values

[Mat Sko]

Hi all,

I have the following situation:

The variables I have selected for a regression contain values for the years 1999-2011, except for the binary variable "poolacc"(= does the property has a swimming pool?) for which I have only values for the year 2011. I believe I have to copy the same values of 2011 to the other years before (1999-2010).

How can I fill in the dots for years 1999-2010 of this variable 'poolacc' with the values of this particular year 2011 in an efficient way?

Here is some data with 3 variables; the id (I generated myself) ; hhmove=year moved into house; poolacc=presence of a pool (1= Yes, 2=No).

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id int hhmove byte poolacc
  1 2001 .
  2 1999 .
  3 1999 .
  4 1999 .
  5 1999 .
  6 2000 .
  7 1999 .
  8 2000 .
  9 2000 .
 10 1999 .
 11 2000 .
 12 2001 .
 13 2000 .
 14 1999 .
 15 2001 .
 16 2001 .
 17 1999 .
 18 1999 .
 19 1999 .
 20 1999 .
 21 1999 .
 22 2000 .
 23 2001 .
 24 2000 .
 25 2000 .
 26 1999 .
 27 2000 .
 28 1999 .
 29 1999 .
 30 1999 .
 31 1999 .
 32 2000 .
 33 2001 .
 34 1999 .
 35 2000 .
 36 2000 .
 37 2000 .
 38 2001 .
 39 2000 .
 40 1999 .
 41 2001 .
 42 1999 .
 43 2001 .
 44 2001 .
 45 2000 .
 46 2001 .
 47 2001 .
 48 2001 .
 49 2000 .
 50 1999 .
 51 1999 .
 52 1999 .
 53 2000 .
 54 1999 .
 55 1999 .
 56 2001 .
 57 1999 .
 58 2001 .
 59 1999 .
 60 1999 .
 61 2000 .
 62 1999 .
 63 1999 .
 64 2001 .
 65 1999 .
 66 2000 .
 67 1999 .
 68 2000 .
 69 1999 .
 70 1999 .
 71 2001 .
 72 2000 .
 73 2000 .
 74 2000 .
 75 2001 .
 76 1999 .
 77 2001 .
 78 2000 .
 79 1999 .
 80 1999 .
 81 2000 .
 82 1999 .
 83 2001 .
 84 1999 .
 85 2001 .
 86 2001 .
 87 2000 .
 88 1999 .
 89 2000 .
 90 2000 .
 91 2000 .
 92 2000 .
 93 2000 .
 94 2000 .
 95 2001 .
 96 2000 .
 97 2001 .
 98 2000 .
 99 1999 .
100 2000 .
end

I tried the following code, but the data remained unchanged...:

bysort id (hhmove): replace poolacc = poolacc[_n -1] if poolacc ==. & hhmove < 2011

and

by id (hhmove), sort: replace poolacc= poolacc[_n-1] if poolacc>= . & hhmove<2011

(retrieved from the Stata website).

Does anyone know which code I need to put in to solve this issue?


Thanks in advance!

[William Lisowski]

First posted, with a subsequent response, at

https://www.statalist.org/forums/for...missing-values

[Stefano Grillini]

Hi all,

Slightly different question on this topic. I am working on a trade data set indexed by three variables: exporter, importer and time. Although the panel is defined by the unique combinations of exporter-importer and time, I need to do some preliminary transformations. One of these is to include rows for missing values, i.e. when in a given year there are not recorded observations for a country pair, such as for example country A exporting to B but country B not exporting to A.

To do this, I use:

fillin iso3_o iso3_d year
drop if iso3_o == iso3_d

Where iso3_o and iso3_d are exporter and importer respectively (Trade with itself is redundant). However, since zero trade needs to enter some models:

replace exports = 0 if exports == .

This commands create a strongly balanced panel for each pair with rows of missing values for all variables created with fillin in the data set except export = 0. For instance:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3(iso3_o iso3_d) int year double(gdp_o exports) byte _fillin
"ABW" "LTU" 2011          . 0 1
"ABW" "KOS" 2011          . 0 1
"ABW" "GAB" 2011          . 0 1
"ABW" "ARG" 2011 2584463616 0 0
"ABW" "PCN" 2011          . 0 1
"ABW" "NIU" 2011          . 0 1
"ABW" "IOT" 2011          . 0 1
"ABW" "VCT" 2011          . 0 1
"ABW" "PER" 2011 2584463616 0 0
"ABW" "BHS" 2011 2584463616 0 0
"ABW" "HND" 2011 2584463616 0 0
"ABW" "CYM" 2011          . 0 1
"ABW" "MAC" 2011          . 0 1
"ABW" "MEX" 2011 2584463616 0 0
"ABW" "LBN" 2011          . 0 1
"ABW" "GIB" 2011          . 0 1
"ABW" "TTO" 2011 2584463616 0 0
"ABW" "LBR" 2011          . 0 1
"ABW" "VUT" 2011          . 0 1
"ABW" "CXR" 2012          . 0 1
end

What I want to do is to replace missing values generated with fillin for the variable gdp_o in non missing values for the same variable for the same year for all iso3_o. Then the same procedure needs to be applied for other variables in the data set. I've tried levelsof in a loop, but it doesn't really work. Also, the command:

bysort iso3_o (year): replace gdp_o = gdp_o[_n-1] if mi(gdp_o)

But I want to make sure it doesn't replace gdp_o from a previous year if the nearest observation is in t-1.

Any thoughts on this?

Thanks

Stefano

[Attaullah Shah]

Are you asking for something like this

net install fillmissing, from(http://fintechprofessor.com)
bysort iso3_o year: fillmissing gdp_o

list
     +--------------------------------------------------------+
     | iso3_o   iso3_d   year       gdp_o   exports   _fillin |
     |--------------------------------------------------------|
  1. |    ABW      LTU   2011   2.584e+09         0         1 |
  2. |    ABW      KOS   2011   2.584e+09         0         1 |
  3. |    ABW      GAB   2011   2.584e+09         0         1 |
  4. |    ABW      ARG   2011   2.584e+09         0         0 |
  5. |    ABW      PCN   2011   2.584e+09         0         1 |
     |--------------------------------------------------------|
  6. |    ABW      NIU   2011   2.584e+09         0         1 |
  7. |    ABW      IOT   2011   2.584e+09         0         1 |
  8. |    ABW      VCT   2011   2.584e+09         0         1 |
  9. |    ABW      PER   2011   2.584e+09         0         0 |
 10. |    ABW      BHS   2011   2.584e+09         0         0 |
     |--------------------------------------------------------|
 11. |    ABW      HND   2011   2.584e+09         0         0 |
 12. |    ABW      CYM   2011   2.584e+09         0         1 |
 13. |    ABW      MAC   2011   2.584e+09         0         1 |
 14. |    ABW      MEX   2011   2.584e+09         0         0 |
 15. |    ABW      LBN   2011   2.584e+09         0         1 |
     |--------------------------------------------------------|
 16. |    ABW      GIB   2011   2.584e+09         0         1 |
 17. |    ABW      TTO   2011   2.584e+09         0         0 |
 18. |    ABW      LBR   2011   2.584e+09         0         1 |
 19. |    ABW      VUT   2011   2.584e+09         0         1 |
 20. |    ABW      CXR   2012           .         0         1 |
     +--------------------------------------------------------+

More on fillmissing here https://fintechprofessor.com/2019/12...lues-in-stata/

[Stefano Grillini]

Thanks a lot Attaullah, that's exactly what I was looking for.