** Version 2/28/23

clear
cap log close
cap program drop _all

global state_files ".../Learning Model Data Files"
global output ".../Output"

program main

	create_shares

end

program create_shares

	//School files for school-level output
	local School_School_list "Alabama Alaska Arizona Arkansas California Colorado Florida Georgia Hawaii Idaho Illinois Indiana Kentucky Louisiana Maine Maryland Michigan Minnesota Mississippi Missouri Nebraska Nevada NewHampshire NewMexico NewYork NorthCarolina NorthDakota Oregon RhodeIsland SouthDakota Texas Utah Vermont WestVirginia Wisconsin" 

	local School_District_list ""
	
	//District files for district-level output
	local District_District_list "Alabama Colorado Connecticut Delaware DC Idaho Kansas Maryland Massachusetts Michigan Minnesota Missouri NewJersey NorthCarolina NorthDakota Ohio Pennsylvania SouthCarolina SouthDakota Tennessee Virginia Washington WestVirginia Wyoming"
	
	//School files to collapse to district-level
	local District_School_list "Alaska Arizona Arkansas California Florida Georgia Hawaii Illinois Indiana Kentucky Louisiana Maine Mississippi Nebraska Nevada NewHampshire NewMexico NewYork Oregon RhodeIsland Texas Utah Vermont Wisconsin"

	
	
	foreach level in "District" "School" {
			clear
		save xtemp, replace emptyok
	
	foreach type in "District" "School" {
		foreach state of local `level'_`type'_list {
	
	di "`state'"
	
	import excel using "$state_files\\`type' Files\\`state'_`type's_LearningModelData_Final.xlsx", firstrow

	
	capture confirm string variable TimePeriodStart
            if !_rc {
				gen PeriodStartDate =date(TimePeriodStart,"MDY",2050)

                }
			else {
				gen PeriodStartDate = TimePeriodStart

			}
			
	

	capture confirm string variable TimePeriodEnd
            if !_rc {
				gen PeriodEndDate =date(TimePeriodEnd,"MDY",2050)
                }
			else {
				gen PeriodEndDate = TimePeriodEnd
			}
	drop TimePeriodEnd TimePeriodStart

	capture confirm string variable NCESDistrictID
		if !_rc {
			destring NCESDistrictID, replace
		}

	capture confirm string variable NCESSchoolID
		if !_rc {
			destring NCESSchoolID, replace
		}
		
	format NCESSchoolID %14.0g
	format NCESDistrictID %12.0g
	foreach var in StateAssignedSchoolID StateAssignedDistrictID{
		capture confirm numeric variable `var'
			if !_rc {
				format `var' %16.0g
			}
	}

foreach var in SchoolName DistrictType SchoolType StateAssignedSchoolID  LearningModel LearningModelGrK5 LearningModelGr68 LearningModelGr912 LearningModelStateCat LearningModelStateCatGrK5 LearningModelStateCatGr68 LearningModelStateCatGr912 StateAssignedDistrictID {		
	capture confirm string variable `var'
		if _rc {
			tostring `var', replace usedisplayformat
		}		
	}
	
	

foreach var in SchoolName SchoolType StateAssignedSchoolID  LearningModel LearningModelGrK5 LearningModelGr68 LearningModelGr912 LearningModelStateCat LearningModelStateCatGrK5 LearningModelStateCatGr68 LearningModelStateCatGr912 {
		replace `var'="" if `var'=="."
}

foreach var in EnrollmentTotal EnrollmentInPerson EnrollmentHybrid EnrollmentVirtual StaffCount StaffCountInPerson {		
	capture confirm string variable `var'
		if !_rc {
			destring `var', i(",") replace
		}		
	}

drop if SchoolName=="" & DistrictName==""

replace LearningModel="In-person" if LearningModel=="In-Person"

	order StateName StateAbbrev DataLevel Charter SchoolName SchoolType NCESSchoolID StateAssignedSchoolID DistrictName DistrictType NCESDistrictID StateAssignedDistrictID TimePeriodInterval PeriodStartDate PeriodEndDate EnrollmentTotal LearningModel LearningModelGrK5 LearningModelGr68 LearningModelGr912 LearningModelStateCat LearningModelStateCatGrK5 LearningModelStateCatGr68 LearningModelStateCatGr912 EnrollmentInPerson EnrollmentHybrid EnrollmentVirtual StaffCount StaffCountInPerson
	
		
	append using xtemp
	save xtemp, replace
	clear	
	
}
	}

	use xtemp, clear

	
	drop if mi(NCES`level'ID)
	drop if mi(LearningModel)
	drop if EnrollmentTotal==.
	
	label define learnmode 1 "Closed" 2 "Virtual" 3 "Hybrid" 4 "In-person"
	encode LearningModel, gen(LearningModel_num) label(learnmode)
	
	gen inperson = LearningModel_num == 4
	gen hybrid = LearningModel_num == 3
	gen virtual = LearningModel_num == 2
	gen closed = LearningModel_num == 1
	drop if closed==1
	gen total = 1
	
	foreach mode in inperson virtual hybrid total { 
			gen `mode'_students = `mode' * EnrollmentTotal
		}
	
	collapse (sum) *students (firstnm) `level'Name StateAbbrev, by(NCES`level'ID) 

	
	foreach mode in inperson hybrid virtual {
			gen share_`mode'=`mode'_students/total_students
		}
	sort StateAbbrev NCES`level'ID
	export delimited StateAbbrev NCES*ID `level'Name share* using "$output/`level'_Overall_Shares.csv", replace

	}	
	

end

main