clear cap log close cap program drop _all global nces_data "C:\Data_Hub\NCES" global state_files "C:\Data_Hub\State_Data_Files" global output_file "C:\Data_Hub\Output" program append_data clear save xtemp, replace emptyok clear //Alabama, Idaho, Minnesota, North Dakota, and West Virginia have both School and District level data available; adjust state_list as needed local state_list "Alabama_Schools Alaska Arizona Arkansas California Colorado Connecticut Florida Idaho_Schools Indiana Kansas Kentucky Louisiana Maine Maryland Massachusetts Michigan Minnesota_Schools Mississippi Missouri Nebraska Nevada NewJersey NewYork NorthDakota_Schools Ohio Oregon RhodeIsland SouthCarolina Texas Utah Vermont Virginia Washington WestVirginia_Schools Wisconsin Wyoming" foreach state of local state_list { di "`state'" import excel using $state_files\\`state'_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=="" 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 save $output_file\appended_data_for_analysis.dta, replace end append_data