Dear All,
I have a panel dataset. I calculate the mean of each variable for each cross-section unit and then export it to a spreadsheet. The data example is given below.
I use the following code to calculate the mean of each variable, and then to export it to an excel sheet.
Running the above code generates the following output.
When I am appending, it is also appending the original data. I can drop them. But it is creating missing observations. I want the excel output should look like the following:
Appreciate your time and help.
Thank you.
I have a panel dataset. I calculate the mean of each variable for each cross-section unit and then export it to a spreadsheet. The data example is given below.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str2 st_code int year float(fam_wel_pc g_fam_wel g_mmi pci) "KA" 2011 0 . . 98567.03 "KA" 2012 0 . -22.580645 103463.02 "KA" 2013 0 . 58.33333 112127.91 "KA" 2014 0 . -15.789474 117844.3 "KA" 2015 0 . -53.125 129488.63 "KA" 2016 0 . 33.333332 145111.56 "KA" 2017 0 . -30 157225.5 "KA" 2018 0 . 21.42857 166098.05 "KA" 2019 0 . 76.47059 173027.55 "KA" 2020 0 . 310 167172.17 "KL" 2011 0 . . 108664.52 "KL" 2012 0 . 7.142857 115158.75 "KL" 2013 0 . -20 119106.17 "KL" 2014 0 . -25 123574.5 "KL" 2015 0 . 44.44444 132114.31 "KL" 2016 0 . -15.384615 141396.64 "KL" 2017 0 . 18.181818 149650.58 "KL" 2018 0 . 53.84615 158562.55 "KL" 2019 0 . 70 163218.1 "KL" 2020 0 . 11.764706 . "TN" 2011 7.868907 . . 103743.36 "TN" 2012 9.597587 22.80702 0 108565.75 "TN" 2013 10.213392 7.142857 56 116020.8 "TN" 2014 13.52631 33.333332 28.20513 120913.7 "TN" 2015 25.12495 87 -20 129999.8 "TN" 2016 21.64589 -13.368984 -15 138528.63 "TN" 2017 13.830707 -35.802467 -8.823529 149716.53 "TN" 2018 20.649124 50 45.16129 160865.6 "TN" 2019 24.505283 19.23077 60 172976.9 "TN" 2020 26.09665 6.989247 37.5 175653.84 end
I use the following code to calculate the mean of each variable, and then to export it to an excel sheet.
Code:
preserve
tempfile building
save `building', emptyok
foreach i in fam_wel_pc g_fam_wel g_mmi pci{
bys st_code : egen mean_`i' = mean(`i')
duplicates drop mean_`i', force
keep st_code mean_`i'
append using `building'
save `"`building'"', replace
}
export excel using temp_output, firstrow(variables) replace
restore
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str2 st_code float(mean_pci mean_g_mmi mean_g_fam_wel mean_fam_wel_pc) int year float(fam_wel_pc g_fam_wel g_mmi pci) "KA" 137012.58 . . . . . . . . "KL" 134605.13 . . . . . . . . "TN" 137698.48 . . . . . . . . "KA" . 42.00785 . . . . . . . "KL" . 16.110596 . . . . . . . "TN" . 20.3381 . . . . . . . "KA" . . . . . . . . . "TN" . . 19.70353 . . . . . . "KA" . . . 0 . . . . . "TN" . . . 17.30588 . . . . . "KA" . . . . 2011 0 . . 98567.03 "KA" . . . . 2012 0 . -22.580645 103463.02 "KA" . . . . 2013 0 . 58.33333 112127.9 "KA" . . . . 2014 0 . -15.789474 117844.3 "KA" . . . . 2015 0 . -53.125 129488.63 "KA" . . . . 2016 0 . 33.333332 145111.56 "KA" . . . . 2017 0 . -30 157225.5 "KA" . . . . 2018 0 . 21.42857 166098.05 "KA" . . . . 2019 0 . 76.47059 173027.55 "KA" . . . . 2020 0 . 310 167172.17 "KL" . . . . 2011 0 . . 108664.52 "KL" . . . . 2012 0 . 7.142857 115158.75 "KL" . . . . 2013 0 . -20 119106.17 "KL" . . . . 2014 0 . -25 123574.5 "KL" . . . . 2015 0 . 44.44444 132114.31 "KL" . . . . 2016 0 . -15.384615 141396.64 "KL" . . . . 2017 0 . 18.181818 149650.58 "KL" . . . . 2018 0 . 53.84615 158562.55 "KL" . . . . 2019 0 . 70 163218.1 "KL" . . . . 2020 0 . 11.764706 . "TN" . . . . 2011 7.868907 . . 103743.36 "TN" . . . . 2012 9.597587 22.80702 0 108565.75 "TN" . . . . 2013 10.213392 7.142857 56 116020.8 "TN" . . . . 2014 13.52631 33.333332 28.20513 120913.7 "TN" . . . . 2015 25.12495 87 -20 129999.8 "TN" . . . . 2016 21.64589 -13.368984 -15 138528.63 "TN" . . . . 2017 13.830707 -35.802467 -8.823529 149716.53 "TN" . . . . 2018 20.649124 50 45.16129 160865.6 "TN" . . . . 2019 24.505283 19.23077 60 172976.9 "TN" . . . . 2020 26.09665 6.989247 37.5 175653.84 end
When I am appending, it is also appending the original data. I can drop them. But it is creating missing observations. I want the excel output should look like the following:
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str2 st_code float(mean_pci mean_g_mmi mean_g_fam_wel mean_fam_wel_pc) "KA" 137012.58 42.00785 . 0 "KL" 134605.13 16.110596 . . "TN" 137698.48 20.3381 19.70353 17.30588 end
Appreciate your time and help.
Thank you.
Comment