use “C:UsersHaigen R HuangDesktopmodata_may15Statamay 20 nightmo_merge_m20.dta”, clear
gen femoney=fe_pct*total_exp/100
gen stmoney= st_pct*total_exp/100
gen lomoney=lo_pct*total_exp/100
sum femoney stmoney lomoney, detail
gen fp_money=femoney/enrol_k12
gen sp_money=stmoney/enrol_k12
gen lp_money=lomoney/enrol_k12
gen p_money=total_exp/enrol_k12
sum fp_money sp_money lp_money p_money, detail
/*
fp_money: mean=1497.048, std.dev=1063.762
sp_money: mean=4781.445, std.dev=2282.123
lp_money: mean=5447.351, std.dev=3654.136
p_money: mean=11725.87, std.dev.=5017.62
*/
/*getting rid of influential data; 15992 observations left*/
keep if(fp_money>0 & fp_money<1497.048+1063.762*3)
keep if(sp_money>0 & sp_money<4781.445+2282.123*3)
keep if(lp_money>0 & sp_money<5447.351+3654.136*3)
sum fp_money sp_money lp_money p_money, detail
browse
rename mean_score1 com_arts
rename mean_score2 math
rename mean_score3 science
rename districtname disname1
rename district_name disname2
save “C:UsersHaigen R HuangDesktopmodata_may15Statamay 20 nightmergmay21.dta”
pwcorr com_arts math lunch_pct white_pct teach_exp admin_sal teach_sal stu_ad_r stu_te_r fp_money sp_money lp_money, sig print(.05)
/*most of them are correlated significantly; but still need to aggregate them because there are different grades included*/
/***reshape to split by year ***/
sort distcode year grade
drop science disname2
gen year2=year
gen min_pct=100-asian_pct-white_pct
drop enrol_k12 lunch asian white disname1 enrollment total_exp stu_tec_r femoney stmoney lomoney p_money white_pct asian_pct
drop fe_pct st_pct lo_pct
reshape wide year math com_arts lunch_pct admin_sal teach_sal teach_exp mast_pct stu_ad_r stu_te_r fp_money sp_money lp_money min_pct, i(distcode grade) j(year2)
/***reshape again to split grades: IT DOES NOT WORK***/
gen g_level=grade
keep if (g_level!=.)
tostring distcode, generate(id)
tostring grade, generate(g_level)
drop distcode
drop year2007 year2008 year2009 year2010 year2011
keep if (grade!=.)
sort distcode grade g_level
reshape wide com_arts2007 math2007 lunch_pct2007 admin_sal2007 teach_sal2007 teach_exp2007 mast_pct2007 stu_ad_r2007 stu_te_r2007 fp_money2007 sp_money2007 lp_money2007 min_pct2007 com_arts2008 math2008 lunch_pct2008 admin_sal2008 teach_sal2008 teach_exp2008 mast_pct2008 stu_ad_r2008 stu_te_r2008 fp_money2008 sp_money2008 lp_money2008 min_pct2008 com_arts2009 math2009 lunch_pct2009 admin_sal2009 teach_sal2009 teach_exp2009 mast_pct2009 stu_ad_r2009 stu_te_r2009 fp_money2009 sp_money2009 lp_money2009 min_pct2009 com_arts2010 math2010 lunch_pct2010 admin_sal2010 teach_sal2010 teach_exp2010 mast_pct2010 stu_ad_r2010 stu_te_r2010 fp_money2010 sp_money2010 lp_money2010 min_pct2010 com_arts2011 math2011 lunch_pct2011 admin_sal2011 teach_sal2011 teach_exp2011 mast_pct2011 stu_ad_r2011 stu_te_r2011 fp_money2011 sp_money2011 lp_money2011 min_pct2011, i(distcode grade) j(g_level)
/***to split grades***/
/*memo: 03grade=1 04grade=2 05grade=3 06grade=4 07grade=5 08grade=6 */
sort distcode grade
keep if (grade!=.)
save “E:Research Projectsmodata_may15Statamay 24long07_11.dta”
keep if (grade<2)
browse
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_3.dta”
use “E:Research Projectsmodata_may15Statamay 24long07_11.dta”, clear
keep if (grade<3 & grade>1)
browse
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_4.dta”
use “E:Research Projectsmodata_may15Statamay 24long07_11.dta”, clear
keep if (grade<4 & grade>2)
browse
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_5.dta”
use “E:Research Projectsmodata_may15Statamay 24long07_11.dta”, clear
keep if (grade<5 & grade>3)
browse
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_6.dta”
use “E:Research Projectsmodata_may15Statamay 24long07_11.dta”, clear
keep if (grade<6 & grade>4)
browse
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_7.dta”
use “E:Research Projectsmodata_may15Statamay 24long07_11.dta”, clear
keep if (grade>5)
browse
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_8.dta”
/***check correlation among variables grade_3 2007***/
pwcorr com_arts2007 math2007 lunch_pct2007 admin_sal2007 teach_sal2007 teach_exp2007 mast_pct2007 stu_ad_r2007 stu_te_r2007 fp_money2007 sp_money2007 lp_money2007 l_min_pct2007, sig print(.05)
/***mostly correlated significantly, thus MR does not fit the data, a SEM model would be a better fit given higly correlation among variables
also confirm with exiting research***/
/***MERG THEM TO BE WIDE***/
use “E:Research Projectsmodata_may15Statamay 24grade_3.dta”, clear
rename com_arts2007 com_2007_03
rename com_arts2008 com_2008_03
rename com_arts2009 com_2009_03
rename com_arts2010 com_2010_03
rename com_arts2011 com_2011_03
rename math2007 math_2007_03
rename math2008 math_2008_03
rename math2009 math_2009_03
rename math2010 math_2010_03
rename math2011 math_2011_03
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_03.dta”
use “E:Research Projectsmodata_may15Statamay 24grade_4.dta”, clear
rename com_arts2007 com_2007_04
rename com_arts2008 com_2008_04
rename com_arts2009 com_2009_04
rename com_arts2010 com_2010_04
rename com_arts2011 com_2011_04
rename math2007 math_2007_04
rename math2008 math_2008_04
rename math2009 math_2009_04
rename math2010 math_2010_04
rename math2011 math_2011_04
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_04.dta”
use “E:Research Projectsmodata_may15Statamay 24grade_5.dta”, clear
rename com_arts2007 com_2007_05
rename com_arts2008 com_2008_05
rename com_arts2009 com_2009_05
rename com_arts2010 com_2010_05
rename com_arts2011 com_2011_05
rename math2007 math_2007_05
rename math2008 math_2008_05
rename math2009 math_2009_05
rename math2010 math_2010_05
rename math2011 math_2011_05
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_05.dta”
use “E:Research Projectsmodata_may15Statamay 24grade_6.dta”, clear
rename com_arts2007 com_2007_06
rename com_arts2008 com_2008_06
rename com_arts2009 com_2009_06
rename com_arts2010 com_2010_06
rename com_arts2011 com_2011_06
rename math2007 math_2007_06
rename math2008 math_2008_06
rename math2009 math_2009_06
rename math2010 math_2010_06
rename math2011 math_2011_06
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_06.dta”
use “E:Research Projectsmodata_may15Statamay 24grade_7.dta”, clear
rename com_arts2007 com_2007_07
rename com_arts2008 com_2008_07
rename com_arts2009 com_2009_07
rename com_arts2010 com_2010_07
rename com_arts2011 com_2011_07
rename math2007 math_2007_07
rename math2008 math_2008_07
rename math2009 math_2009_07
rename math2010 math_2010_07
rename math2011 math_2011_07
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_07.dta”
use “E:Research Projectsmodata_may15Statamay 24grade_8.dta”, clear
rename com_arts2007 com_2007_08
rename com_arts2008 com_2008_08
rename com_arts2009 com_2009_08
rename com_arts2010 com_2010_08
rename com_arts2011 com_2011_08
rename math2007 math_2007_08
rename math2008 math_2008_08
rename math2009 math_2009_08
rename math2010 math_2010_08
rename math2011 math_2011_08
sort distcode
save “E:Research Projectsmodata_may15Statamay 24grade_08.dta”
merge 1:1 distcode using “E:Research Projectsmodata_may15Statamay 24grade_07.dta”
drop _merge
merge 1:1 distcode using “E:Research Projectsmodata_may15Statamay 24grade_06.dta”
drop _merge
merge 1:1 distcode using “E:Research Projectsmodata_may15Statamay 24grade_05.dta”
drop _merge
merge 1:1 distcode using “E:Research Projectsmodata_may15Statamay 24grade_04.dta”
drop _merge
merge 1:1 distcode using “E:Research Projectsmodata_may15Statamay 24grade_03.dta”
drop _merge
drop grade year2007 year2008 year2009 year2010 year2011
save “E:Research Projectsmodata_may15Statamay 24g_year_wide.dta”