Dear all,
I use Stata13 and I have a panel data of 44 countries (c_id 1 to 44) and 6 variables - ps, va, ccr, rol, rq and ge. All the countries have missing values for all the variables for the following years: 1997, 1999 and 2001. I want to create these values using the average of the pre- and post years.
For instance, to generate values of ps:
for year1997 = (ps1996 + ps1998)/2
for year1999 = (ps1998 + ps2000)/2
for year2001 = (ps2000 + ps2001)/2
Is there an easy way to do this in Stata....considering the large sample, it is cumbersome using excel. Any suggestions will do.
Thank you.
(I was unable to attach the Stata file, so I just copied some portion of the data)
c_id country cc year ccr ge ps rol rq va
1 ANGOLA AGO 1996 -1.16 -0.84 -2.10 -1.63 -1.46 -1.64
1 ANGOLA AGO 1997
1 ANGOLA AGO 1998 -1.32 -1.36 -2.34 -1.70 -1.72 -1.44
1 ANGOLA AGO 1999
1 ANGOLA AGO 2000 -1.52 -1.46 -2.08 -1.63 -1.83 -1.48
1 ANGOLA AGO 2001
1 ANGOLA AGO 2002 -1.14 -1.25 -1.61 -1.61 -1.49 -1.24
1 ANGOLA AGO 2003 -1.30 -1.14 -1.03 -1.53 -1.23 -1.27
2 BENIN BEN 1996 -0.93 -0.41 0.96 -0.19 -0.20 0.12
2 BENIN BEN 1997
2 BENIN BEN 1998 -0.60 -0.27 0.66 -0.14 -0.17 0.34
2 BENIN BEN 1999
2 BENIN BEN 2000 -0.49 -0.27 0.72 -0.27 -0.21 0.36
2 BENIN BEN 2001
2 BENIN BEN 2002 -0.79 -0.38 0.74 -0.34 -0.44 -0.00
3 BOTSWANA BWA 1996 0.59 0.47 0.92 0.50 0.76 0.87
3 BOTSWANA BWA 1997
3 BOTSWANA BWA 1998 0.71 0.50 0.87 0.58 0.70 0.73
3 BOTSWANA BWA 1999
3 BOTSWANA BWA 2000 0.67 0.51 0.95 0.53 0.64 0.64
3 BOTSWANA BWA 2001
4 BURKINA FASO BFA 1996 0.22 -1.02 -0.41 -1.03 -0.25 -0.73
4 BURKINA FASO BFA 1997
4 BURKINA FASO BFA 1998 -0.24 -0.75 -0.20 -0.85 -0.39 -0.57
4 BURKINA FASO BFA 1999
4 BURKINA FASO BFA 2000 -0.14 -0.62 -0.01 -0.67 -0.12 -0.36
4 BURKINA FASO BFA 2001
4 BURKINA FASO BFA 2002 -0.02 -0.72 -0.37 -0.68 -0.14 -0.48
5 BURUNDI BDI 1996 -1.39 -1.73 -2.24 -1.72 -1.67 -1.75
5 BURUNDI BDI 1997
5 BURUNDI BDI 1998 -1.15 -1.66 -2.42 -1.48 -1.59 -1.61
5 BURUNDI BDI 1999
5 BURUNDI BDI 2000 -1.01 -1.44 -2.15 -1.56 -1.22 -1.63
5 BURUNDI BDI 2001
5 BURUNDI BDI 2002 -0.94 -1.45 -2.39 -1.39 -1.32 -1.22
6 CAMEROON CMR 1996 -1.16 -1.00 -1.06 -1.47 -1.13 -0.97
6 CAMEROON CMR 1997
6 CAMEROON CMR 1998 -1.04 -0.74 -0.82 -1.12 -0.64 -1.14
6 CAMEROON CMR 1999
6 CAMEROON CMR 2000 -1.06 -0.67 -0.57 -1.23 -0.59 -1.14
6 CAMEROON CMR 2001
6 CAMEROON CMR 2002 -1.09 -0.82 -0.72 -1.25 -0.89 -1.17
.................................................. .................................................. ...............
.................................................. .................................................. ...............
.................................................. .................................................. ..............
43 ZAMBIA ZMB 1996 -1.03 -1.06 -0.23 -0.65 -0.42 -0.40
43 ZAMBIA ZMB 1997
43 ZAMBIA ZMB 1998 -0.88 -0.86 0.09 -0.53 -0.12 -0.60
43 ZAMBIA ZMB 1999
43 ZAMBIA ZMB 2000 -0.85 -0.86 -0.06 -0.54 -0.26 -0.49
43 ZAMBIA ZMB 2001
43 ZAMBIA ZMB 2002 -0.94 -0.81 -0.35 -0.39 -0.61 -0.37
44 ZIMBABWE ZWE 1996 -0.25 -0.23 -0.53 -0.82 -0.98 -0.62
44 ZIMBABWE ZWE 1997
44 ZIMBABWE ZWE 1998 -0.65 -0.37 -0.88 -0.71 -0.79 -0.82
44 ZIMBABWE ZWE 1999
44 ZIMBABWE ZWE 2000 -0.95 -0.78 -1.42 -1.33 -1.46 -1.11
44 ZIMBABWE ZWE 2001
44 ZIMBABWE ZWE 2002 -1.21 -0.90 -1.59 -1.59 -1.97 -1.41
44 ZIMBABWE ZWE 2003 -1.25 -0.95 -1.13 -1.68 -2.00 -1.44
44 ZIMBABWE ZWE 2004 -1.31 -0.95 -1.26 -1.76 -2.03 -1.55
I use Stata13 and I have a panel data of 44 countries (c_id 1 to 44) and 6 variables - ps, va, ccr, rol, rq and ge. All the countries have missing values for all the variables for the following years: 1997, 1999 and 2001. I want to create these values using the average of the pre- and post years.
For instance, to generate values of ps:
for year1997 = (ps1996 + ps1998)/2
for year1999 = (ps1998 + ps2000)/2
for year2001 = (ps2000 + ps2001)/2
Is there an easy way to do this in Stata....considering the large sample, it is cumbersome using excel. Any suggestions will do.
Thank you.
(I was unable to attach the Stata file, so I just copied some portion of the data)
c_id country cc year ccr ge ps rol rq va
1 ANGOLA AGO 1996 -1.16 -0.84 -2.10 -1.63 -1.46 -1.64
1 ANGOLA AGO 1997
1 ANGOLA AGO 1998 -1.32 -1.36 -2.34 -1.70 -1.72 -1.44
1 ANGOLA AGO 1999
1 ANGOLA AGO 2000 -1.52 -1.46 -2.08 -1.63 -1.83 -1.48
1 ANGOLA AGO 2001
1 ANGOLA AGO 2002 -1.14 -1.25 -1.61 -1.61 -1.49 -1.24
1 ANGOLA AGO 2003 -1.30 -1.14 -1.03 -1.53 -1.23 -1.27
2 BENIN BEN 1996 -0.93 -0.41 0.96 -0.19 -0.20 0.12
2 BENIN BEN 1997
2 BENIN BEN 1998 -0.60 -0.27 0.66 -0.14 -0.17 0.34
2 BENIN BEN 1999
2 BENIN BEN 2000 -0.49 -0.27 0.72 -0.27 -0.21 0.36
2 BENIN BEN 2001
2 BENIN BEN 2002 -0.79 -0.38 0.74 -0.34 -0.44 -0.00
3 BOTSWANA BWA 1996 0.59 0.47 0.92 0.50 0.76 0.87
3 BOTSWANA BWA 1997
3 BOTSWANA BWA 1998 0.71 0.50 0.87 0.58 0.70 0.73
3 BOTSWANA BWA 1999
3 BOTSWANA BWA 2000 0.67 0.51 0.95 0.53 0.64 0.64
3 BOTSWANA BWA 2001
4 BURKINA FASO BFA 1996 0.22 -1.02 -0.41 -1.03 -0.25 -0.73
4 BURKINA FASO BFA 1997
4 BURKINA FASO BFA 1998 -0.24 -0.75 -0.20 -0.85 -0.39 -0.57
4 BURKINA FASO BFA 1999
4 BURKINA FASO BFA 2000 -0.14 -0.62 -0.01 -0.67 -0.12 -0.36
4 BURKINA FASO BFA 2001
4 BURKINA FASO BFA 2002 -0.02 -0.72 -0.37 -0.68 -0.14 -0.48
5 BURUNDI BDI 1996 -1.39 -1.73 -2.24 -1.72 -1.67 -1.75
5 BURUNDI BDI 1997
5 BURUNDI BDI 1998 -1.15 -1.66 -2.42 -1.48 -1.59 -1.61
5 BURUNDI BDI 1999
5 BURUNDI BDI 2000 -1.01 -1.44 -2.15 -1.56 -1.22 -1.63
5 BURUNDI BDI 2001
5 BURUNDI BDI 2002 -0.94 -1.45 -2.39 -1.39 -1.32 -1.22
6 CAMEROON CMR 1996 -1.16 -1.00 -1.06 -1.47 -1.13 -0.97
6 CAMEROON CMR 1997
6 CAMEROON CMR 1998 -1.04 -0.74 -0.82 -1.12 -0.64 -1.14
6 CAMEROON CMR 1999
6 CAMEROON CMR 2000 -1.06 -0.67 -0.57 -1.23 -0.59 -1.14
6 CAMEROON CMR 2001
6 CAMEROON CMR 2002 -1.09 -0.82 -0.72 -1.25 -0.89 -1.17
.................................................. .................................................. ...............
.................................................. .................................................. ...............
.................................................. .................................................. ..............
43 ZAMBIA ZMB 1996 -1.03 -1.06 -0.23 -0.65 -0.42 -0.40
43 ZAMBIA ZMB 1997
43 ZAMBIA ZMB 1998 -0.88 -0.86 0.09 -0.53 -0.12 -0.60
43 ZAMBIA ZMB 1999
43 ZAMBIA ZMB 2000 -0.85 -0.86 -0.06 -0.54 -0.26 -0.49
43 ZAMBIA ZMB 2001
43 ZAMBIA ZMB 2002 -0.94 -0.81 -0.35 -0.39 -0.61 -0.37
44 ZIMBABWE ZWE 1996 -0.25 -0.23 -0.53 -0.82 -0.98 -0.62
44 ZIMBABWE ZWE 1997
44 ZIMBABWE ZWE 1998 -0.65 -0.37 -0.88 -0.71 -0.79 -0.82
44 ZIMBABWE ZWE 1999
44 ZIMBABWE ZWE 2000 -0.95 -0.78 -1.42 -1.33 -1.46 -1.11
44 ZIMBABWE ZWE 2001
44 ZIMBABWE ZWE 2002 -1.21 -0.90 -1.59 -1.59 -1.97 -1.41
44 ZIMBABWE ZWE 2003 -1.25 -0.95 -1.13 -1.68 -2.00 -1.44
44 ZIMBABWE ZWE 2004 -1.31 -0.95 -1.26 -1.76 -2.03 -1.55
Comment