/// Nebraska data cleaning /////////////////////////////////////////////////////////////////////////

** step 1
drop if Cohort4YearGraduationStatus=="TO"
recode Cohort4YearGraduationStatus (N=".") (DO=".")

** step 2
gen CohortYear= CohortGradYear-4
tab CohortYear, mis

///////////////  test type re-categories ///////////////
tab G8_MathType, mis

browse G8_MathType G8_MathScore if G8_MathType=="NULL"
browse G8_MathType G8_MathScore if G8_MathScore=="0"

gen G8_MathType_re=.
replace G8_MathType_re=1 if G8_MathType=="NESA"
replace G8_MathType_re=1 if G8_MathType=="NESA   "
replace G8_MathType_re=2 if G8_MathType=="NESA_AA"
replace G8_MathType_re=3 if G8_MathType=="STARS"

tab G8_MathType_re, mis nol


label define mathtype 1 "NESA" 2 "NESA_AA" 3 "STARS"
label values G8_MathType_re mathtype
tab G8_MathType_re, mis
tab G8_MathType CohortGradYear, mis

tab G8_MathType_re CohortYear, mis

********** reading test type **************

tab G8_ReadingType, mis

gen G8_ReadingType_re=.
replace G8_ReadingType_re=1 if G8_ReadingType=="NESA"
replace G8_ReadingType_re=1 if G8_ReadingType=="NESA   "
replace G8_ReadingType_re=2 if G8_ReadingType=="NESA_AA"
replace G8_ReadingType_re=3 if G8_ReadingType=="STARS"

label values G8_ReadingType_re mathtype
tab G8_ReadingType_re, mis

///////////// STARS test mean and SD //////////////

destring G8_MathScore G8_ReadingScore, replace ignore(NULL)
tab1 G8_MathScore G8_ReadingScore, mis

sum G8_MathScore if G8_MathType_re==3 & G8_MathScore!=.
tab G8_MathScore if G8_MathType_re==3 & G8_MathScore!=.
* 40425		3.384094    .5812475


************ reading ***************
sum G8_ReadingScore if G8_ReadingType_re==3
* 20038		3.428236    .4827552

///////////////  mean and SD //////////////

************ math *************
/// STARS
sum G8_MathScore if G8_MathType_re==3 & CohortYear==2009 & G8_MathScore!=.
tab G8_MathScore if G8_MathType_re==3 & CohortYear==2009 & G8_MathScore!=.
* 20005    3.382152    .5774819
sum G8_MathScore if G8_MathType_re==3 & CohortYear==2010 & G8_MathScore!=.
tab G8_MathScore if G8_MathType_re==3 & CohortYear==2010 & G8_MathScore!=.
* 20420    3.385996     .584921

//// NESA
sum G8_MathScore if G8_MathType_re==1 & CohortYear==2011
*  20700    97.42014    40.11236
sum G8_MathScore if G8_MathType_re==1 & CohortYear==2012
*  20966    98.82963    37.58963
sum G8_MathScore if G8_MathType_re==1 & CohortYear==2013
*  21286    101.7108    38.03553


************* reading ***************

//// STARS
sum G8_ReadingScore if G8_ReadingType_re==3 & CohortYear==2009
* 20038		3.428236    .4827552


//// NESA
sum G8_ReadingScore if G8_ReadingType_re==1 & CohortYear==2010
*  20074    101.9233    38.07408
sum G8_ReadingScore if G8_ReadingType_re==1 & CohortYear==2011
tab G8_ReadingScore if G8_ReadingType_re==1 & CohortYear==2011
*  20673    105.6612    38.52276 
sum G8_ReadingScore if G8_ReadingType_re==1 & CohortYear==2012
*  20931    108.4616    37.36105
sum G8_ReadingScore if G8_ReadingType_re==1 & CohortYear==2013
*  21250     114.751    40.57591


//////// get the Z scores /////

*************** math **************


****** NESA *******
gen math_2013=(G8_MathScore-101.7)/38 if G8_MathType_re==1 & CohortYear==2013
gen math_2012=(G8_MathScore-98.8)/37.6 if G8_MathType_re==1 & CohortYear==2012
gen math_2011=(G8_MathScore-97.4)/40.1 if G8_MathType_re==1 & CohortYear==2011

****** STARS *******
gen math_2010=(G8_MathScore-3.385996)/.584921 if G8_MathType_re==3 & CohortYear==2010
gen math_2009=(G8_MathScore-3.382152)/.5774819 if G8_MathType_re==3 & CohortYear==2009


sum math_*


************** reading ***************

********** NESA **********
gen reading_2013=(G8_ReadingScore-114.8)/40.6 if G8_ReadingType_re==1 & CohortYear==2013
gen reading_2012=(G8_ReadingScore-108.5)/37.4 if G8_ReadingType_re==1 & CohortYear==2012
gen reading_2011=(G8_ReadingScore-105.7)/38.5 if G8_ReadingType_re==1 & CohortYear==2011
gen reading_2010=(G8_ReadingScore-101.9)/38.1 if G8_ReadingType_re==1 & CohortYear==2010

****** STARS *******
gen reading_2009=(G8_ReadingScore-3.428236)/.4827552 if G8_ReadingType_re==3 & CohortYear==2009

sum reading_*

****** generate Z scores **************

gen math_zscore=.
replace math_zscore=math_2009 if CohortYear==2009
replace math_zscore=math_2010 if CohortYear==2010
replace math_zscore=math_2011 if CohortYear==2011
replace math_zscore=math_2012 if CohortYear==2012
replace math_zscore=math_2013 if CohortYear==2013
sum math_*


gen reading_zscore=.
replace reading_zscore=reading_2009 if CohortYear==2009
replace reading_zscore=reading_2010 if CohortYear==2010
replace reading_zscore=reading_2011 if CohortYear==2011
replace reading_zscore=reading_2012 if CohortYear==2012
replace reading_zscore=reading_2013 if CohortYear==2013
sum reading_*

gen state=0

** step 3 recode awards

* recode 2y award type1
gen ps2y_at1=ps2y_awardtype1

replace ps2y_at1="0" if substr(ps2y_awardtype1,1,19) == "HIGH SCHOOL DIPLOMA" | substr(ps2y_awardtype1,1,13) == "UNDERGRADUATE"

replace ps2y_at1="0" if ps2y_awardtype1=="NULL" & ps2y_graduated==0

replace ps2y_at1="1" if substr(ps2y_awardtype1,1,14) == "1-YEAR DIPLOMA" | substr(ps2y_awardtype1,1,2) == "AA" | substr(ps2y_awardtype1,1,5) == "ASSOC" | substr(ps2y_awardtype1,1,18) == "CAREER CERTIFICATE" | substr(ps2y_awardtype1,1,4) == "CERT" | substr(ps2y_awardtype1,1,7) == "DIPLOMA" | substr(ps2y_awardtype1,1,8) == "PATHWAYS" | substr(ps2y_awardtype1,1,9) == "TECHNICAL" | substr(ps2y_awardtype1,1,10) == "VOCATIONAL" | substr(ps2y_awardtype1,1,7) == "COLLEGE" | substr(ps2y_awardtype1,1,6) == "CREDIT" | substr(ps2y_awardtype1,1,5) == "CYBER" | substr(ps2y_awardtype1,1,3) == "EMT" | substr(ps2y_awardtype1,1,4) == "LESS" | substr(ps2y_awardtype1,1,5) == "OWATC" | substr(ps2y_awardtype1,1,5) == "STAND"

replace ps2y_at1="2" if substr(ps2y_awardtype1,1,2) == "BA" | substr(ps2y_awardtype1,1,8) == "BACHELOR" | substr(ps2y_awardtype1,1,2) == "BS"

replace ps2y_at1="3" if substr(ps2y_awardtype1,1,6) == "MASTER"

replace ps2y_at1="" if ps2y_awardtype1=="NULL" & ps2y_graduated==1

tab1 ps2y_at1, mis

* recode 2y award type2
gen ps2y_at2=ps2y_awardtype2

replace ps2y_at2="0" if ps2y_awardtype2=="NULL" & ps2y_graduated==0

replace ps2y_at2="1" if substr(ps2y_awardtype2,1,5) == "ASSOC" | substr(ps2y_awardtype2,1,18) == "CAREER CERTIFICATE" | substr(ps2y_awardtype2,1,4) == "CERT" | substr(ps2y_awardtype2,1,7) == "DIPLOMA" | substr(ps2y_awardtype2,1,9) == "TECHNICAL" 

replace ps2y_at2="2" if substr(ps2y_awardtype2,1,2) == "BS"

replace ps2y_at2="" if ps2y_awardtype2=="NULL" & ps2y_graduated==1

tab1 ps2y_at2, mis

* recode 2y award type3
gen ps2y_at3=ps2y_awardtype3

replace ps2y_at3="0" if ps2y_awardtype3=="NULL" & ps2y_graduated==0

replace ps2y_at3="1" if substr(ps2y_awardtype3,1,5) == "ASSOC" | substr(ps2y_awardtype3,1,18) == "CAREER CERTIFICATE" | substr(ps2y_awardtype3,1,4) == "CERT" | substr(ps2y_awardtype3,1,7) == "DIPLOMA"

replace ps2y_at3="" if ps2y_awardtype3=="NULL" & ps2y_graduated==1

tab1 ps2y_at3, mis

//////  ps5y award types  ////////

*tab1 ps5y_awardtype*, mis

* recode 2y award type1
gen ps5y_at1=ps5y_awardtype1

replace ps5y_at1="0" if substr(ps5y_awardtype1,1,19) == "HIGH SCHOOL DIPLOMA" | substr(ps5y_awardtype1,1,5) == "MINOR" | substr(ps5y_awardtype1,1,13) == "UNDERGRADUATE" | substr(ps5y_awardtype1,1,6) == "SECOND" | substr(ps5y_awardtype1,1,10) == "UNIVERSITY" | substr(ps5y_awardtype1,1,4) == "NO C" | substr(ps5y_awardtype1,1,2) == "SB" 

replace ps5y_at1="0" if ps5y_awardtype1=="NULL" & ps5y_graduated==0

replace ps5y_at1="1" if substr(ps5y_awardtype1,1,14) == "1-YEAR DIPLOMA" | substr(ps5y_awardtype1,1,3) == "A.A" | substr(ps5y_awardtype1,1,2) == "AA" | substr(ps5y_awardtype1,1,2) == "AB" | substr(ps5y_awardtype1,1,6) == "ARTIUM" | substr(ps5y_awardtype1,1,5) == "ASSOC" | substr(ps5y_awardtype1,1,6) == "CAREER" | substr(ps5y_awardtype1,1,4) == "CERT" | substr(ps5y_awardtype1,1,7) == "DIPLOMA" | substr(ps5y_awardtype1,1,8) == "PATHWAYS" | substr(ps5y_awardtype1,1,9) == "TECHNICAL" | substr(ps5y_awardtype1,1,10) == "VOCATIONAL" | substr(ps5y_awardtype1,1,2) == "AS" | substr(ps5y_awardtype1,1,4) == "COMP" | substr(ps5y_awardtype1,1,2) == "CP" | substr(ps5y_awardtype1,1,6) == "CREDIT" | substr(ps5y_awardtype1,1,3) == "DCE" | substr(ps5y_awardtype1,1,4) == "DIGI" | substr(ps5y_awardtype1,1,4) == "SSOC"

replace ps5y_at1="2" if substr(ps5y_awardtype1,1,2) == "BA" | substr(ps5y_awardtype1,1,3) == "B A" | substr(ps5y_awardtype1,1,4) == "B.A." | substr(ps5y_awardtype1,1,5) == "B B A" | substr(ps5y_awardtype1,1,3) == "BBA" | substr(ps5y_awardtype1,1,8) == "BACHELOR" | substr(ps5y_awardtype1,1,3) == "B S" | substr(ps5y_awardtype1,1,4) == "B.S." | substr(ps5y_awardtype1,1,2) == "BS" | substr(ps5y_awardtype1,1,8) == "BBA" | substr(ps5y_awardtype1,1,3) == "BFA" | substr(ps5y_awardtype1,1,6) == "B.F.A." | substr(ps5y_awardtype1,1,5) == "B F A" | substr(ps5y_awardtype1,1,2) == "BM" | substr(ps5y_awardtype1,1,6) == "B.L.S."

replace ps5y_at1="2" if substr(ps5y_awardtype1,1,1) == "B" | substr(ps5y_awardtype1,1,4) == "CHRI" | substr(ps5y_awardtype1,1,3) == "EAR" | substr(ps5y_awardtype1,1,3) == "ENG" | substr(ps5y_awardtype1,1,3) == "GEN" | substr(ps5y_awardtype1,1,4) == "GRAD" | substr(ps5y_awardtype1,1,3) == "K-1" | substr(ps5y_awardtype1,1,4) == "LUTH" 

replace ps5y_at1="3" if substr(ps5y_awardtype1,1,6) == "MASTER" | substr(ps5y_awardtype1,1,4) == "M.S." | substr(ps5y_awardtype1,1,3) == "MBA" | substr(ps5y_awardtype1,1,3) == "MAT" 

replace ps5y_at1="" if ps5y_awardtype1=="NULL" & ps5y_graduated==1

tab1 ps5y_at1, mis


*tab1 ps5y_awardtype2 ps5y_awardtype3, mis


*recode 5y type2
gen ps5y_at2=ps5y_awardtype2

replace ps5y_at2="0" if substr(ps5y_awardtype2,1,13) == "UNDERGRADUATE" | substr(ps5y_awardtype2,1,10) == "UNIVERSITY"

replace ps5y_at2="0" if ps5y_awardtype2=="NULL" & ps5y_graduated==0

replace ps5y_at2="1" if substr(ps5y_awardtype2,1,5) == "ASSOC" | substr(ps5y_awardtype2,1,18) == "CAREER CERTIFICATE" | substr(ps5y_awardtype2,1,4) == "CERT" | substr(ps5y_awardtype2,1,7) == "DIPLOMA" | substr(ps5y_awardtype2,1,8) == "PATHWAYS" | substr(ps5y_awardtype2,1,9) == "TECHNICAL"

replace ps5y_at2="2" if substr(ps5y_awardtype2,1,2) == "BA" | substr(ps5y_awardtype2,1,8) == "BACHELOR" | substr(ps5y_awardtype2,1,2) == "BS" | substr(ps5y_awardtype2,1,3) == "BSE" | substr(ps5y_awardtype2,1,3) == "BSN" | substr(ps5y_awardtype2,1,6) == "B.F.A." | substr(ps5y_awardtype2,1,5) == "B F A" | substr(ps5y_awardtype2,1,2) == "BM" | substr(ps5y_awardtype2,1,6) == "B.L.S." | substr(ps5y_awardtype2,1,4) == "B.S." | substr(ps5y_awardtype2,1,3) == "BBA" 

replace ps5y_at2="3" if substr(ps5y_awardtype2,1,1) == "M"  

replace ps5y_at2="" if ps5y_awardtype2=="NULL" & ps5y_graduated==1

tab1 ps5y_at2, mis

*recode 5y type3 
gen ps5y_at3=ps5y_awardtype3

replace ps5y_at3="0" if ps5y_awardtype3=="NULL" & ps5y_graduated==0

replace ps5y_at3="1" if substr(ps5y_awardtype3,1,5) == "ASSOC" | substr(ps5y_awardtype3,1,4) == "CERT" | substr(ps5y_awardtype3,1,7) == "DIPLOMA"

replace ps5y_at3="2" if substr(ps5y_awardtype3,1,8) == "BACHELOR" | substr(ps5y_awardtype3,1,2) == "BS"

replace ps5y_at3="3" if substr(ps5y_awardtype3,1,6) == "MASTER"

replace ps5y_at3="" if ps5y_awardtype3=="NULL" & ps5y_graduated==1

tab ps5y_at3, mis

save nde_all_merged, replace

/// South Dakota data cleaning ///////////////////////////////////////////////////////////////////////
** step 1 career cluster

gen cluster1=concentratoragriculturefood
gen cluster2=concentratorarchitectureco
gen cluster3=concentratorartsavtechnol
gen cluster4=concentratorbusinessmanageme
gen cluster5=concentratoreducationtrain
gen cluster6=concentratorfinance

gen cluster8=concentratorhealthscience 
gen cluster9=concentratorhospitalitytou 
gen cluster10=concentratorhumanservices 
gen cluster11=concentratorinformationtechn
 
gen cluster13=concentratormanufacturing
gen cluster14=concentratormarketing 
gen cluster15=concentratorsciencetechnolo 
gen cluster16=concentratortransportationd

global xlist cluster1-cluster16
  
foreach var of varlist $xlist {
    replace `var'="1" if `var'=="Y"
	replace `var'="0" if `var'=="N"
}

destring $xlist, replace

egen a=rowtotal($xlist)
tab a, m

gen ctecareercluster=.
replace ctecareercluster=1 if cluster1==1 & a<2
replace ctecareercluster=2 if cluster2==1 & a<2
replace ctecareercluster=3 if cluster3==1 & a<2
replace ctecareercluster=4 if cluster4==1 & a<2
replace ctecareercluster=5 if cluster5==1 & a<2
replace ctecareercluster=6 if cluster6==1 & a<2
replace ctecareercluster=8 if cluster8==1 & a<2
replace ctecareercluster=9 if cluster9==1 & a<2
replace ctecareercluster=10 if cluster10==1 & a<2
replace ctecareercluster=11 if cluster11==1 & a<2
replace ctecareercluster=13 if cluster13==1 & a<2
replace ctecareercluster=14 if cluster14==1 & a<2
replace ctecareercluster=15 if cluster15==1 & a<2
replace ctecareercluster=16 if cluster16==1 & a<2
replace ctecareercluster=17 if a>1

tab ctecareercluster, m

** step 2 rename variables
rename uniqueid studentid
rename districtid districtcode
rename schoolid schoolcode
gen cohortgradyear=substr (cohortyear,5,4)
destring cohortgradyear, replace
rename yearhighschoolgraduate cohort4yeargraduationstatus
rename specialedstatus8thgrade g8_sped
rename frlstatus8thgrade g8_frl
rename englishlearnerstatus8thgrade g8_el
rename readingasessmentname8thgrade g8_readingtype
rename readingscalescore8thgrade g8_readingscore
rename mathasessmentname8thgrade g8_mathtype
rename mathscalescore8thgrade g8_mathscore
rename cteconcentratorstatus cteconcentrator
rename at2yearsoutfrommayofgradu ps2y_enrollment
rename ai ps2y_fulltime
rename at2yearsoutweretheyevera ps2y_4yrcollege
rename ak ps2y_2yrcollege
rename al 
rename at2yearsoutdidtheygraduat ps2y_graduated
rename iftheydidgraduatein2years ps2y_awardtype1
rename ao ps2y_awarddate1
rename at5yearsoutfrommayofgradu ps5y_enrollment
rename aq ps5y_fulltime
rename at5yearsoutweretheyevera ps5y_2yrcollege
rename as at ps5y_4yrcollege
rename at5yearsoutdidtheygraduat ps5y_graduated
rename iftheydidgraduatein5years ps5y_awardtype1
rename aw ps5y_awarddate1
gen districtcteconcentratorclusters=.

** step 3 district career cluster
/// first mannually grouped district cluster data in five years into one Excel file, then used Stata

merge m:m districtid using sd_cluster_all
tab state _merge, mis

drop if _merge==2
browse if _merge==1

destring districtcteconcentratorclusters, replace
replace districtcteconcentratorclusters=numberofclusters_1213 if cohortgradyear==2013 & state==2 
replace districtcteconcentratorclusters=numberofclusters_1314 if cohortgradyear==2014 & state==2
replace districtcteconcentratorclusters=numberofclusters_1415 if cohortgradyear==2015 & state==2
replace districtcteconcentratorclusters=numberofclusters_1516 if cohortgradyear==2016 & state==2
replace districtcteconcentratorclusters=numberofclusters_1617 if cohortgradyear==2017 & state==2

order districtid districtcode districtname district_1617 , first

drop numberofclusters_1617-_merge
drop district_1617

save sd_all, replace

** step 4 standardize reading and math scores

use sd_all

gen cohortyear= cohortgradyear_re-4
tab cohortyear, mis

///////////////  test type re-categories ///////////////
tab g8_mathtype, mis

gen g8_mathtype_re=.
replace g8_mathtype_re=1 if g8_mathtype=="dstep"
replace g8_mathtype_re=2 if g8_mathtype=="stepa"

label define mathtype 1 "dstep" 2 "stepa" 
label values g8_mathtype_re mathtype

tab g8_mathtype_re, mis

********** reading test type **************

tab g8_readingtype, mis

gen g8_readingtype_re=.
replace g8_readingtype_re=1 if g8_readingtype=="dstep"
replace g8_readingtype_re=2 if g8_readingtype=="stepa"

label values g8_readingtype_re mathtype

tab g8_readingtype_re, mis


///////////////  mean and sd //////////////

************ math *************

sum g8_mathscore if g8_mathtype_re==1 & cohortyear==2009

tab g8_mathscore if g8_mathtype_re==1 & cohortyear==2009 & g8_mathscore!=.
* 8443    715.8174    40.03369
sum g8_mathscore if g8_mathtype_re==1 & cohortyear==2010 & g8_mathscore!=.
tab g8_mathscore if g8_mathtype_re==1 & cohortyear==2010 & g8_mathscore!=.
* 8357    719.5275    39.34709

sum g8_mathscore if g8_mathtype_re==1 & cohortyear==2011
*  8313    720.3962    40.68368 
sum g8_mathscore if g8_mathtype_re==1 & cohortyear==2012
*  8166    722.2415    43.38349 
sum g8_mathscore if g8_mathtype_re==1 & cohortyear==2013
*  8385    718.0692    41.72003


************* reading ***************

sum g8_readingscore if g8_readingtype_re==1 & cohortyear==2009
* 8423    622.3255     33.5476
sum g8_readingscore if g8_readingtype_re==1 & cohortyear==2010
*  8346    626.3468    35.64801 
sum g8_readingscore if g8_readingtype_re==1 & cohortyear==2011
tab g8_readingscore if g8_readingtype_re==1 & cohortyear==2011
*  8299    624.9428    35.35212   
sum g8_readingscore if g8_readingtype_re==1 & cohortyear==2012
*  8155    625.1841    37.31324
sum g8_readingscore if g8_readingtype_re==1 & cohortyear==2013
*  8363     623.831    36.87948


///////////////////// get the z scores //////////////////////


///////////////////////////////////
*************** math **************
///////////////////////////////////


gen math_2013=(g8_mathscore-718.0692)/41.72003 if g8_mathtype_re==1 & cohortyear==2013
gen math_2012=(g8_mathscore-722.2415)/43.38349 if g8_mathtype_re==1 & cohortyear==2012
gen math_2011=(g8_mathscore-720.3962)/35.35212 if g8_mathtype_re==1 & cohortyear==2011
gen math_2010=(g8_mathscore-719.5275)/39.34709 if g8_mathtype_re==1 & cohortyear==2010
gen math_2009=(g8_mathscore-715.8174)/40.03369 if g8_mathtype_re==1 & cohortyear==2009

sum math_*



//////////////////////////////////////
************** reading ***************
//////////////////////////////////////


gen reading_2013=(g8_readingscore-623.831)/36.87948 if g8_readingtype_re==1 & cohortyear==2013
gen reading_2012=(g8_readingscore-625.1841)/37.31324 if g8_readingtype_re==1 & cohortyear==2012
gen reading_2011=(g8_readingscore-624.9428)/35.35212 if g8_readingtype_re==1 & cohortyear==2011
gen reading_2010=(g8_readingscore-626.3468)/35.64801 if g8_readingtype_re==1 & cohortyear==2010
gen reading_2009=(g8_readingscore-622.3255)/33.5476 if g8_readingtype_re==1 & cohortyear==2009

sum reading_*

////////////////////////////////////////////////

gen math_zscore=.
replace math_zscore=math_2009 if cohortyear==2009
replace math_zscore=math_2010 if cohortyear==2010
replace math_zscore=math_2011 if cohortyear==2011
replace math_zscore=math_2012 if cohortyear==2012
replace math_zscore=math_2013 if cohortyear==2013
sum math_*
tab1 math_*, mis

gen reading_zscore=.
replace reading_zscore=reading_2009 if cohortyear==2009
replace reading_zscore=reading_2010 if cohortyear==2010
replace reading_zscore=reading_2011 if cohortyear==2011
replace reading_zscore=reading_2012 if cohortyear==2012
replace reading_zscore=reading_2013 if cohortyear==2013
sum reading_*

sum g8_mathscore if math_zscore==.
sum g8_readingscore if reading_zscore==.

gen state==1

* recode 2y award type1
gen ps2y_at1=ps2y_awardtype1

replace ps2y_at1="0" if substr(ps2y_awardtype1,1,13) == "UNDERGRADUATE" | substr(ps2y_awardtype1,1,8) == "MICHIGAN"

replace ps2y_at1="1" if substr(ps2y_awardtype1,1,14) == "1-YEAR DIPLOMA" | substr(ps2y_awardtype1,1,6) == "2-YEAR" | substr(ps2y_awardtype1,1,2) == "AA" | substr(ps2y_awardtype1,1,3) == "AAS" | substr(ps2y_awardtype1,1,2) == "AS" | substr(ps2y_awardtype1,1,5) == "ASSOC" | substr(ps2y_awardtype1,1,3) == "CER" | substr(ps2y_awardtype1,1,6) == "CREDIT" | substr(ps2y_awardtype1,1,7) == "DIPLOMA" | substr(ps2y_awardtype1,1,9) == "TECHNICAL" 

replace ps2y_at1="2" if substr(ps2y_awardtype1,1,8) == "BACHELOR" | substr(ps2y_awardtype1,1,2) == "BS"| substr(ps2y_awardtype1,1,2) == "BA" 

replace ps2y_at1="" if ps2y_awardtype1=="UNSPECIFIED"

tab1 ps2y_at1, mis

tab ps2y_at1 ps2y_graduated, mis

*tab ps2y_at1 ps2y_graduated, mis

* recode 2y award type2
gen ps2y_at2=ps2y_awardtype2

replace ps2y_at2="1" if substr(ps2y_awardtype2,1,6) == " ASSOC" | substr(ps2y_awardtype2,1,5) == " CERT" | substr(ps2y_awardtype2,1,7) == " CREDIT" | substr(ps2y_awardtype2,1,8) == " DIPLOMA" | substr(ps2y_awardtype2,1,10) == " TECHNICAL" | substr(ps2y_awardtype2,1,5) == " NURS" | substr(ps2y_awardtype2,1,6) == " TRAIN"


tab1 ps2y_at2, mis

*check graduated as yes and awards as missing
tab ps2y_at2 ps2y_graduated, mis
tab ps2y_at2 ps2y_graduated if ps2y_at1=="", mis

* recode 2y award type3
gen ps2y_at3=ps2y_awardtype3

replace ps2y_at3="1" if substr(ps2y_awardtype3,1,6) == " ASSOC" | substr(ps2y_awardtype3,1,5) == " CERT" | substr(ps2y_awardtype3,1,8) == " DIPLOMA" | substr(ps2y_awardtype3,1,10) == " TECHNICAL" 

tab1 ps2y_at3, mis

*check graduated as yes and awards as missing
tab ps2y_at3 ps2y_graduated, mis
tab ps2y_at3 ps2y_graduated if ps2y_at1=="", mis


* recode 2y award type4
gen ps2y_at4=ps2y_awardtype4

replace ps2y_at4="1" if substr(ps2y_awardtype4,1,5) == " CERT" | substr(ps2y_awardtype4,1,8) == " DIPLOMA" | substr(ps2y_awardtype4,1,10) == " TECHNICAL" 

tab1 ps2y_at4, mis

*check graduated as yes and awards as missing
tab ps2y_at4 ps2y_graduated, mis
tab ps2y_at4 ps2y_graduated if ps2y_at1=="", mis

browse ps2y_awardtype1 ps2y_awardtype2 ps2y_awardtype3 ps2y_awardtype4 if ps2y_at4=="1"
* for those 4 stu has ps2y_awardtype4, ps2y_awardtype3 are the same with ps2y_awardtype4, so I'll drop them before append to NDE recoded data.
drop ps2y_awardtype4 ps2y_at4

//////  ps5y award types  ////////

*tab1 ps5y_awardtype*, mis

* recode 2y award type1
gen ps5y_at1=ps5y_awardtype1
*replace ps5y_at1="0" if substr(ps5y_awardtype1,1,19) == "HIGH SCHOOL DIPLOMA" | substr(ps5y_awardtype1,1,5) == "MINOR" | substr(ps5y_awardtype1,1,13) == "UNDERGRADUATE" | substr(ps5y_awardtype1,1,6) == "SECOND" | substr(ps5y_awardtype1,1,10) == "UNIVERSITY"

replace ps5y_at1="0" if substr(ps5y_awardtype1,1,4) == "BUSI" 

replace ps5y_at1="1" if substr(ps5y_awardtype1,1,14) == "1-YEAR DIPLOMA" | substr(ps5y_awardtype1,1,6) == "2-YEAR" | substr(ps5y_awardtype1,1,2) == "AA" | substr(ps5y_awardtype1,1,2) == "AB" | substr(ps5y_awardtype1,1,6) == "ARTIUM" | substr(ps5y_awardtype1,1,2) == "AS" | substr(ps5y_awardtype1,1,4) == "CERT" | substr(ps5y_awardtype1,1,7) == "DIPLOMA" | substr(ps5y_awardtype1,1,9) == "TECHNICAL" | substr(ps5y_awardtype1,1,10) == "VOCATIONAL" | substr(ps5y_awardtype1,1,3) == "DTA" | substr(ps5y_awardtype1,1,3) == "5-8" | substr(ps5y_awardtype1,1,3) == "ADV" | substr(ps5y_awardtype1,1,3) == "DEN" | substr(ps5y_awardtype1,1,3) == "EME" | substr(ps5y_awardtype1,1,3) == "OCC" 

replace ps5y_at1="2" if substr(ps5y_awardtype1,1,3) == "B A" | substr(ps5y_awardtype1,1,3) == "B C" | substr(ps5y_awardtype1,1,3) == "B D" | substr(ps5y_awardtype1,1,3) == "B E" | substr(ps5y_awardtype1,1,3) == "B S" | substr(ps5y_awardtype1,1,4) == "B.A." | substr(ps5y_awardtype1,1,5) == "B.I.D" | substr(ps5y_awardtype1,1,5) == "B.L.A" | substr(ps5y_awardtype1,1,4) == "B.S." | substr(ps5y_awardtype1,1,2) == "BA" | substr(ps5y_awardtype1,1,3) == "BBA" | substr(ps5y_awardtype1,1,3) == "BFA" | substr(ps5y_awardtype1,1,3) == "BGS" | substr(ps5y_awardtype1,1,2) == "BS" 

replace ps5y_at1="3" if substr(ps5y_awardtype1,1,6) == "MASTER"

replace ps5y_at1="" if ps5y_awardtype1=="UNSPECIFIED"

tab1 ps5y_at1, mis

tab ps5y_at1 ps5y_graduated, mis


*recode 5y type2
gen ps5y_at2=ps5y_awardtype2

replace ps5y_at2="0" if substr(ps5y_awardtype2,1,6) == " MINOR" | substr(ps5y_awardtype2,1,6) == " UNDER" | substr(ps5y_awardtype2,1,5) == " NO C" | substr(ps5y_awardtype2,1,5) == " MEDI" | substr(ps5y_awardtype2,1,4) == " NUR" | substr(ps5y_awardtype2,1,4) == " VET" 

replace ps5y_at2="1" if substr(ps5y_awardtype2,1,7) == " 1-YEAR" | substr(ps5y_awardtype2,1,3) == " AS" | substr(ps5y_awardtype2,1,4) == " CER" | substr(ps5y_awardtype2,1,8) == " DIPLOMA" | substr(ps5y_awardtype2,1,10) == " TECHNICAL" | substr(ps5y_awardtype2,1,11) == " VOCATIONAL" | substr(ps5y_awardtype2,1,4) == " DTA" | substr(ps5y_awardtype2,1,4) == " C1." | substr(ps5y_awardtype2,1,4) == " COA" | substr(ps5y_awardtype2,1,4) == " HUM" | substr(ps5y_awardtype2,1,5) == " KIND"  

replace ps5y_at2="2" if substr(ps5y_awardtype2,1,4) == " B S" | substr(ps5y_awardtype2,1,4) == " BAC" | substr(ps5y_awardtype2,1,3) == " BA" | substr(ps5y_awardtype2,1,3) == " BS" | substr(ps5y_awardtype2,1,4) == " BIR" | substr(ps5y_awardtype2,1,5) == " GRAD" | substr(ps5y_awardtype2,1,5) == " LUTH" 

replace ps5y_at2="3" if substr(ps5y_awardtype2,1,7) == " MASTER" | substr(ps5y_awardtype2,1,3) == " M " | substr(ps5y_awardtype2,1,5) == " M.A." | substr(ps5y_awardtype2,1,4) == " MAT" | substr(ps5y_awardtype2,1,4) == " MOT" 


save sd_all_merged, replace

/// Combine two states data //////////////////////////////////////////////////////////////////////////

use nde_all_merged

append sd_all_merged, force

save multi_states, replace

/// Combine with CCD data  ////////////////////////////////////////////////////////////////////////// 

use ccd

keep if StateAbbrPublicSchoolLates=="NE " | StateAbbrPublicSchoolLates=="SD "
count

rename TotalStudentsAllGradesExclu tot_stu_2017
rename AZ tot_stu_2016
rename BA tot_stu_2015
rename BB tot_stu_2014
rename BC tot_stu_2013
rename FreeandReducedLunchStudents	frlp_stu_2017
rename BE frlp_stu_2016
rename BF frlp_stu_2015
rename BG frlp_stu_2014
rename BH frlp_stu_2013

destring tot_stu_*, replace
destring frlp_stu_*, replace

*use the graduate year data
gen frlp_p_2017=frlp_stu_2017/tot_stu_2017
gen frlp_p_2016=frlp_stu_2016/tot_stu_2016
gen frlp_p_2015=frlp_stu_2015/tot_stu_2015
gen frlp_p_2014=frlp_stu_2014/tot_stu_2014
gen frlp_p_2013=frlp_stu_2013/tot_stu_2013

tab urbancentriclocalepublicsch, mis
gen locale=.
replace locale=12 if urbancentriclocalepublicsch=="City: Large"
replace locale=11 if urbancentriclocalepublicsch=="City: Mid-size"
replace locale=10 if urbancentriclocalepublicsch=="13-City: Small"
replace locale=9 if urbancentriclocalepublicsch=="21-Suburb: Large"
replace locale=8 if urbancentriclocalepublicsch=="22-Suburb: Mid-size"
replace locale=7 if urbancentriclocalepublicsch=="23-Suburb:Small"
replace locale=6 if urbancentriclocalepublicsch=="31-Town: Fringe"
replace locale=5 if urbancentriclocalepublicsch=="32-Town: Distant"
replace locale=4 if urbancentriclocalepublicsch=="33-Town: Remote"
replace locale=3 if urbancentriclocalepublicsch=="41-Rural: Fringe"
replace locale=2 if urbancentriclocalepublicsch=="42-Rural: Distant"
replace locale=1 if urbancentriclocalepublicsch=="43-Rural: Remote"

tab urbancentriclocalepublicsch locale, mis

gen schoolcode=substr(schoolidncesassignedpubli, 4, 9)
drop schoolidncesassignedpubli

merge 1:1 schoolcode using multi_states

keep if _merge==3

keep if schooltypepublicschool2017=="1-Regular school"

save multi_states_ccd, replace

/// Combine with CRCD data  //////////////////////////////////////////////////////////////////////////

use crcd

keep if lea_state=="NE" | lea_state=="SD"

drop sch_dual_ind-sch_dualenr_idea_f 

replace schoolcode=substr(schid, 4, 2) if lea_state=="SD"

save jj_2, replace

merge 1:1 schoolcode using jj

drop if jj=="No"

save multi_states_ccd_jj, replace

/// Propensity Score Matching  //////////////////////////////////////////////////////////////////////////
use multi_states_ccd_jj, clear

gen stratum=1 if state==0 & cohortyear==2009
replace stratum=2 if state==0 & cohortyear==2010
replace stratum=3 if state==0 & cohortyear==2011
replace stratum=4 if state==0 & cohortyear==2012
replace stratum=5 if state==0 & cohortyear==2013

replace stratum=6 if state==1 & cohortyear==2009
replace stratum=7 if state==1 & cohortyear==2010
replace stratum=8 if state==1 & cohortyear==2011
replace stratum=9 if state==1 & cohortyear==2012
replace stratum=10 if state==1 & cohortyear==2013

forvalues s = 1/10 {						/* This creates a looping variable "s" with values 1 thru 10  and starts the loop at "{" */ 
		use cte_prepsm_new_8_27    					/* opens the data */ 
		psmatch2 cteconcentrator gender dummy_amindian dummy_asian dummy_black dummy_hispanic dummy_twopacislander g8_sped g8_frl math_zscore reading_zscore frlp_p b(last).locale if stratum==`s', noreplacement  /* runs the ps matching for each strata where `s' = loop var */ 
		drop if _weight!=1					/* drops unmatched records */ 
		save cte_stratum_`s', replace  		/* saves the matched stratum data to a separate dataset */ 
		clear
		}  			/* restarts the loop */ 

* append the datasets across the strata
append using cte_stratum_1 cte_stratum_2 cte_stratum_3 cte_stratum_4 cte_stratum_5 cte_stratum_6 cte_stratum_7 cte_stratum_8 cte_stratum_9 cte_stratum_10 
* checks of data 
tab _weight stratum 
tab cteconcentrator stratum

save CTE_psm, replace