Hi Statalist,

I'm trying to append, rename, format and generate new variables. may goals are to;
1. rename variables "SCH_ID" "School_id" and "SchoolID into "schoolid" since these data has same content.
2. generate data source from each of the tempfile
3. destring var "PSCGC_MUN" with string from other sources. My code below

clear all
import excel "...\SY2017-2018 EnrollmentDB.xlsx", sheet("ES") cellrange(A2:AU2366) firstrow
tempfile sy2017_18enrol_es
save `sy2017_18enrol_es', replace
clear all
import excel "...\SY2017-2018 EnrollmentDB.xlsx", sheet("SS") cellrange(A2:BC483) firstrow
tempfile sy2017_18enrol_ss
save `sy2017_18enrol_ss', replace
clear all
import excel "...\SY2018-2019 EnrollmentDB.xlsx", sheet("ES") cellrange(A2:BN2320) firstrow
tempfile sy2018_19enrol_es
save `sy2018_19enrol_es', replace
clear
import excel "...\SY2018-2019 EnrollmentDB.xlsx", sheet("SS") cellrange(A2:BF472) firstrow
tempfile sy2018_19enrol_ss
save `sy2018_19enrol_ss', replace
clear
import excel ...\SY 2019-2020.xlsx", sheet("BARMM") cellrange(A5:CA2723) firstrow
tempfile sy2019_20enrol_es_ss
save `sy2019_20enrol_es_ss', replace
clear
set more off
import excel "...\SY 2020-2021 Enrolment.xlsx", sheet("Sheet1") firstrow
tempfile sy2020_21enrol_es_ss
save `sy2020_21enrol_es_ss', replace
clear all
set more off
local forms `sy2017_18enrol_es' `sy2017_18enrol_ss' `sy2018_19enrol_es' `sy2018_19enrol_ss' `sy2019_20enrol_es_ss' `sy2020_21enrol_es_ss'
foreach file of local forms {
local datasource "`file'"
foreach v in "`file'" {
capture confirm var SCH_TYPE
capture confirm var SCH_ID
capture confirm var School_id
capture confirm numeric var PSCGC_MUN
if !_rc {
capture ren SCH_TYPE SchoolType
capture ren SCH_ID SchoolID
capture ren School_id SchoolID
capture destring PSCGC_MUN, replace
}
gen data=`datasource'
append using "`file'" //don't want to use force here
}
}

Thank you in advance,