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
endI 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
restoreCode:
* 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
endWhen 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
endAppreciate your time and help.
Thank you.
0 Response to Exporting Stata output to excel
Post a Comment