clear
set more off

********************************************************************************
********************************************************************************
*	Import demographic data
********************************************************************************
********************************************************************************


import excel "$source\demographics\estim-pop-dep-sexe-aq-1975-2021.xlsx", sheet("2021") cellrange(A4:BM111) firstrow allstring clear


renvars Département B \ departement_code departement_name

foreach var of varlist Ensemble-W{
local text = subinstr(`var'[1],"ans","",1)
if regexm(`var',"à"){
local head = substr("`text'",1,strpos("`text'","à")-1)
	local head = subinstr("`head'"," ","",.)
di "`head'"
local tail = substr("`text'",strpos("`text'","à")+2,.)
	local tail = subinstr("`tail'"," ","",.)
	di "`tail'"
ren `var' pop_`head'_`tail'
continue
}
if regexm(`var',"95 ans et plus"){
ren `var' pop_95_plus
continue
}
if regexm(`var',"Total"){
ren `var' pop_total
continue
}
}

keep departement_code pop_*
drop if departement_code == ""

drop if length(departement_code) != 2

destring pop_*, replace
compress

egen sum_60_more = rowtotal(pop_60_64-pop_95_plus)
gen prop_60_more = sum_60_more / pop_total

egen sum_65_more = rowtotal(pop_65_69-pop_95_plus)
gen prop_65_more = sum_65_more / pop_total

ren pop_total population

keep departement_code prop_* population
save "$temp/departement_population", replace
