hope you are all safe and in good health.
Kindly I have balanced panel data with missing values for some years, I am interested in two specific years, where I want to replace years (e.i. 2000 and 2015) with values from previous or onwards years, in particular:
1- I want to replace the year 2000 (if no value) with a value from the closest onward years till 2006 e.g. 2001, (if no value available in 2001), then from 2002, (if no value, then till ... 2006), the year 2000 will have no value in case years 2001 to 2006 don't have any values.
2- For the year 2015, I want to replace (if no value) with a value from the closest years, first by going backward from 2014 to 2010, in case of no values for years 2014 to 2016, then I want to replace 2015 (if no value), by going onward from 2016 till 2018, the year 2015 will have no value in case the years 2010 till the latest year don't have any value.
- The dataset has 22 countries for 167 indicators (seriescode) spanning the period 2000 to 2019, kindly find below an example of the dataset
- The variable id is the combination of (goal target indicator seriescode concatenate) using group command in egen.
- dum_year is a dummy variable denoting 1 if year <2007, 2 if year between 2010 and 2015, 3 if year 2016 and above.
- Years 2007 to 2009 are dropped.
I tried the following code to fill/ replace the year 2000, but it didn't work:
Code:
bys geoarename id: replace value=value[_n+1] if value==. & dum+_year==1
Thank you so much.
stay safe,
Rabih
----------------------- copy starting from the next line -----------------------
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input byte goal float dum_year str5 target str7 indicator str17 seriescode str20 geoareaname float(timeperiod value id) str42 concatenate 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2000 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2001 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2002 20.6 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2003 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2004 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2005 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2006 . 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2010 . 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2011 . 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2012 18.3 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2013 22.7 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2014 . 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2015 . 1 ";;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2016 . 1 ";;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2017 17.1 1 ";;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2018 . 1 ";;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_DAY1" "Djibouti" 2019 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2000 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2001 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2002 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2003 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2004 1.1 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2005 .9 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2006 .3 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2010 .2 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2011 .2 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2012 . 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2013 . 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2014 . 1 ";;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2015 . 1 ";;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2016 1 1 ";;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2017 . 1 ";;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2018 . 1 ";;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_DAY1" "State of Palestine" 2019 . 1 ";;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2006 .1 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2005 .2 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2004 .3 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2003 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2002 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2001 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2000 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2015 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2014 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2013 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2012 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2011 .1 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2010 0 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2019 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2018 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2017 .2 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_EMP1" "State of Palestine" 2016 . 2 "15+;BOTHSEX;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2006 .3 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2005 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2004 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2003 .9 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2002 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2001 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 1 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2000 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2015 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2014 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2013 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2012 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2011 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 2 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2010 .2 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2019 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2018 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2017 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" 1 3 "1.1" "1.1.1" "SI_POV_EMP1" "Jordan" 2016 . 10 "25+;MALE;;;;;;;;;;;;;;;;G;" end
0 Response to Replacing missing values in a panel data
Post a Comment