****************************************************************************************************************************************
****************************************************************************************************************************************
**************************************** Iván Auciello Estévez - Banco de España *******************************************************
*********************************************** Madrid - 20/09/2022 ********************************************************************
****************************************************************************************************************************************
****************************************************************************************************************************************

set varabbrev off
clear all
set more off
set maxvar 12000

*****************************************************************************************************************************************
//DESCRIPTION: This Dofile will allow you to merge automatically those EFF waves (Encuesta Financiera de las Familias), you would like to use for your analysis. The code retains the selected waves in a panel format, ensuring the IDs are standardized across all waves.
*****************************************************************************************************************************************

*****************************************************************************************************************************************
//HOW TO USE IT?
//		1) You have to change the current directory to the one you have the data sets saved
//		2) Select the first and the last wave you want to use 
//			2.1) Remember that the only waves currently available are the following ones: 2002 2005 2008 2011 2014 2017 2020
//		3) Execute the entire code in one go
*****************************************************************************************************************************************


//Choose your path
global path "C:\"
cd "${path}"

//Choose the waves (w_min = the 1st wave you want to use; w_max = the last wave you want to use) 
global w_min 2002
global w_max 2020




*********************************************************************************
*********************************************************************************
//Changing the values of the different IDs
*********************************************************************************
*********************************************************************************


//I just keep the IDs variables in order to use less computter power and all the variables could fit in the Stata Matrix
forvalues i = $w_min (3) $w_max {
	if `i' == 2002 {
		u "$path/other_sections_2002_imp1.dta", clear
		keep h_*
		gen h2_`i'= h_number
		save "$path/cross_walk_ID_2002.dta"
	}
	
	if `i'!=2002 {
		u "$path/otras_secciones_`i'_imp1.dta", clear
		keep h_*
		gen h2_`i'= h_`i'
		save "$path/cross_walk_ID_`i'.dta"
		
	}
}



//Change the values of the different IDs in order to have no duplicates across waves
forvalues i = $w_min (3) $w_max{
	
	//cada ola hay que añadir un if nuevo correspondiente a la nueva ola
	if `i'==2020{
			u "$path/cross_walk_ID_`i'.dta", clear
			
			
			replace h_2020=h_2020+70000
			replace h_2017=h_2017+60000 if h_2017!=.
			sort h_2017

			sum h_2017
			local max=r(max)
			replace h_2017 = h_2017[_n-1] + 1 if missing(h_2017) 
			replace h_2017 = h_2017 + 600000 if h_2017>`max'
			replace h_2017 = h_2017 - `max' if h_2017>=600000

			rename * *_2020
			rename h_2020_2020 h_2020
			rename h_2017_2020 h_2017

			rename h2_2020_2020 h2_2020

			

			save "$path/cross_walk_ID_`i'.dta", replace
	}

	if `i'==2017{
			u "$path/cross_walk_ID_`i'.dta", clear
			
			replace h_2014=3575 if h_2017==4299 & h_2014==59 //EDITAMOS EL HOGAR SEPARADO PARA CORREGIR EL HOGAR AL QUE APUNTA EN LA OLA ANTERIOR
			*duplicates drop h_2014, force //ATENCIÓN: AQUI DEJO A ELECCIÓN DEL PROGRAMA CUAL DROPEAR POR LO QUE ESA OBSERVACIÓN NO SE MATCHEARÁ

			replace h_2017=h_2017+60000
			replace h_2014=h_2014+50000 if h_2014!=.
			sort h_2014

			sum h_2014
			local max=r(max)
			replace h_2014 = h_2014[_n-1] + 1 if missing(h_2014) 
			replace h_2014 = h_2014 + 500000 if h_2014>`max'
			replace h_2014 = h_2014 - `max' if h_2014>=500000

			rename * *_2017
			rename h_2017_2017 h_2017
			rename h_2014_2017 h_2014

			rename h2_2017_2017 h2_2017

			


			save "$path/cross_walk_ID_`i'.dta", replace
	}
	
	if `i'==2014{
			u "$path/cross_walk_ID_`i'.dta", clear
			
			*duplicates drop h_2014, force //ATENCIÓN: AQUI DEJO A ELECCIÓN DEL PROGRAMA CUAL DROPEAR POR LO QUE ESA OBSERVACIÓN NO SE MATCHEARÁ

			
			replace h_2014=h_2014+50000 
			replace h_2011=h_2011+40000 if h_2011!=. 
			sort h_2011

			sum h_2011
			local max=r(max)
			replace h_2011 = h_2011[_n-1] + 1 if missing(h_2011) 
			replace h_2011 = h_2011 + 400000 if h_2011>`max'
			replace h_2011 = h_2011 - `max' if h_2011>=400000

			rename * *_2014
			rename h_2014_2014 h_2014
			rename h_2011_2014 h_2011

			rename h2_2014_2014 h2_2014


			save "$path/cross_walk_ID_`i'.dta", replace
	
	}
	
	if `i'==2011{
			u "$path/cross_walk_ID_`i'.dta", clear


			
			replace h_2011=h_2011+40000 
			replace h_2008=h_2008+30000 if h_2008!=. 

			sort h_2008

			sum h_2008
			local max=r(max)
			replace h_2008 = h_2008[_n-1] + 1 if missing(h_2008) 
			replace h_2008 = h_2008 + 300000 if h_2008>`max'
			replace h_2008 = h_2008 - `max' if h_2008>=300000

			rename * *_2011
			rename h_2011_2011 h_2011
			rename h_2008_2011 h_2008

			rename h2_2011_2011 h2_2011

			save "$path/cross_walk_ID_`i'.dta", replace

	}
	
	if `i'==2008{
			u "$path/cross_walk_ID_`i'.dta", clear

			replace h_2005=. if h_2008==1123 //IMPORTANT: We edit the divorced household to ensure it is not a panel.
			*duplicates drop h_2005, force //NOTE: Here, the program chooses which observation to drop, meaning this observation will not be matched.
			
			replace h_2008=h_2008+30000 
			replace h_2005=h_2005+20000 if h_2005!=. 

			sort h_2005

			sum h_2005
			local max=r(max)
			replace h_2005 = h_2005[_n-1] + 1 if missing(h_2005) 
			replace h_2005 = h_2005 + 200000 if h_2005>`max'
			replace h_2005 = h_2005 - `max' if h_2005>=200000

			rename * *_2008
			rename h_2008_2008 h_2008
			rename h_2005_2008 h_2005

			rename h2_2008_2008 h2_2008
			
			
			save "$path/cross_walk_ID_`i'.dta", replace

		
	}
	
	if `i'==2005{
			u "$path/cross_walk_ID_`i'.dta", clear

			*duplicates drop h_2005, force //ATENCIÓN: AQUI DEJO A ELECCIÓN DEL PROGRAMA CUAL DROPEAR POR LO QUE ESA OBSERVACIÓN NO SE MATCHEARÁ

			replace h_2005=h_2005+20000 
			rename h_number h_2002
			replace h_2002=h_2002+10000 if h_2002!=. 

			sort h_2002

			sum h_2002
			local max=r(max)
			replace h_2002 = h_2002[_n-1] + 1 if missing(h_2002) 
			replace h_2002 = h_2002 + 100000 if h_2002>`max'
			replace h_2002 = h_2002 - `max' if h_2002>=100000

			rename * *_2005
			rename h_2005_2005 h_2005
			rename h_2002_2005 h_2002

			rename h2_2005_2005 h2_2005



			save "$path/cross_walk_ID_`i'.dta", replace
					
	}
	
	if `i'==2002{
			u "$path/cross_walk_ID_`i'.dta", clear

			rename h_number h_2002

			replace h_2002=h_2002+10000 

			rename * *_2002
			rename h_2002_2002 h_2002

			rename h2_2002_2002 h2_2002

			save "$path/cross_walk_ID_`i'.dta", replace

				
	}
}

*********************************************************************************
*********************************************************************************
//Merging all datasets
*********************************************************************************
*********************************************************************************

foreach i in $w_min {
	if `i' == 2002{
		u "$path/cross_walk_ID_`i'.dta", clear
		global w_max_1 = $w_max - 3
		local counter = 2000000
		
		forvalues w = $w_min (3) $w_max_1 {
		local w2 = `w' + 3
		
		merge 1:1 h_`w' using "$path/cross_walk_ID_`w2'.dta", gen(merge_`w2')

		sort h_`w2'
		sum h_`w2'
		local max=r(max)
		replace h_`w2' = h_`w2'[_n-1] + 1 if missing(h_`w2') 
		replace h_`w2' = h_`w2' + `counter' if h_`w2'>`max'
		replace h_`w2' = h_`w2' - `max' if h_`w2'>=`counter'
		
		local counter = `counter' + 1000000
		}
	}
	
	if `i' > 2002 {
				u "$path/cross_walk_ID_`i'.dta", clear
		global w_max_1 = $w_max - 3
		local counter = 2000000
		
		forvalues w = $w_min (3) $w_max_1 {
		local w2 = `w' + 3
		
		merge 1:1 h_`w' using "$path/cross_walk_ID_`w2'.dta", gen(merge_`w2')

		sort h_`w2'
		sum h_`w2'
		local max=r(max)
		replace h_`w2' = h_`w2'[_n-1] + 1 if missing(h_`w2') 
		replace h_`w2' = h_`w2' + `counter' if h_`w2'>`max'
		replace h_`w2' = h_`w2' - `max' if h_`w2'>=`counter'
		
		local counter = `counter' + 1000000
		}
		
	}
		
}


************************************************************************************************************
************************************************************************************************************
//Take out the artificial values created before and set them as missings.
//Generate the unique ID and do the reshape to set the database in long format.
************************************************************************************************************
************************************************************************************************************

//Set missing values
forvalues i = $w_min (3) $w_max{
replace h_`i'=. if h_`i'>=100000
}


//Drop the first ID variable (h_`w_min-3')
foreach i in $w_min{
	if `i' > 2002{
		local w_min_1 = $w_min - 3
		drop h_`w_min_1'
	}
}


//Homogenizing the IDs
global w_max_1 = $w_max - 3
forvalues m = $w_min (3) $w_max_1{
			
			local m_1 = `m' + 3
			replace h_`m_1' = h_`m' if h_`m'!=. & h_`m_1'!=.		
}
	
	
//Generating an observation counter (needed for the reshape)
gen i=1 if _n==1
replace i = i[_n-1] + 1 if missing(i) 

*order *, alphabetic


//Creating the list with all the unique variable names
forvalues i = $w_min (3) $w_max{
unab vars`i' : *`i' 
local stubs`i' : subinstr local vars`i' "`i'" "", all
}

local stubs `stubs2020' `stubs2017' `stubs2014' `stubs2011' `stubs2008' `stubs2005' `stubs2002' 
local uniquestubs : list uniq stubs


//Reshape
reshape long `uniquestubs', i(i) j(year)	


drop if h_==.
rename *_ *
rename h ID
drop i

//Show the number of observation per wave and the # of times in the panel per HH
sort ID year
by ID: gen num_panel=_N
tab num_panel year

tab num_panel 


save "$path/cross_walk_long.dta"




forvalues imp = 1 (1) 5{
	u "$path/cross_walk_long.dta", clear
	gen implicate = `imp'
	save "$path/cross_walk_long_imp`imp'"
	
}

u "$path/cross_walk_long_imp1"
forvalues imp = 2(1)5{
	append using "$path/cross_walk_long_imp`imp'"
}
save "$path/cross_walk_long.dta", replace



************************************************************************************************


//Change the name of the identificator variable and generate a year variable in order to do ther merges
forvalues imp = 1(1)5{
forvalues i = $w_min (3) $w_max {
	if `i' == 2002{
		u "$path/other_sections_`i'_imp`imp'.dta", clear
		rename h_number h2
		gen year =2002
		gen implicate=`imp'
		save "$path/other_sections_`i'_imp`imp'.dta", replace
		
		u "$path/section6_`i'_imp`imp'.dta",clear 
		rename h_number h2
		gen year = `i'
		gen implicate=`imp'
		save "$path/section6_`i'_imp`imp'.dta", replace 
		}
	
	
	if `i' != 2002 {
		u "$path/otras_secciones_`i'_imp`imp'.dta", clear
		rename h_`i' h2
		gen year = `i'
		gen implicate=`imp'
		save "$path/otras_secciones_`i'_imp`imp'.dta", replace
		
		u "$path/seccion6_`i'_imp`imp'.dta",clear 
		rename h_`i' h2
		gen year = `i'
		gen implicate=`imp'
		save "$path/seccion6_`i'_imp`imp'.dta", replace 
		
	}
		
}
}






forvalues imp = 1(1)5{
	foreach i in $w_min{
		if `i' == 2002{
			global w_max_2 = $w_max - 3
			u "$path/other_sections_${w_min}_imp`imp'.dta", clear
			forvalues w = $w_min (3) $w_max_2 {
				local w2 = `w' + 3
				qui: append using "$path/otras_secciones_`w2'_imp`imp'.dta"



		   }
		}
		if `i' != 2002{
			global w_max_2 = $w_max - 3
			u "$path/otras_secciones_${w_min}_imp`imp'.dta", clear
			forvalues w = $w_min (3) $w_max_2 {
				local w2 = `w' + 3
				qui: append using "$path/otras_secciones_`w2'_imp`imp'.dta"



       }
    }
	save "$path/otras_secciones_all_imp`imp'.dta", replace
}
}



u  "$path/otras_secciones_all_imp1.dta", clear
forvalues imp =2(1)5{
    qui: append using "$path/otras_secciones_all_imp`imp'.dta"
}

save "$path/otras_secciones_all.dta", replace







forvalues imp = 1(1)5{
	foreach i in $w_min{
		if `i' == 2002{
			global w_max_2 = $w_max - 3
			u "$path/section6_${w_min}_imp`imp'.dta", clear
			forvalues w = $w_min (3) $w_max_2 {
				local w2 = `w' + 3
				qui: append using "$path/seccion6_`w2'_imp`imp'.dta"



		   }
		}
		if `i' != 2002{
			global w_max_2 = $w_max - 3
			u "$path/seccion6_${w_min}_imp`imp'.dta", clear
			forvalues w = $w_min (3) $w_max_2 {
				local w2 = `w' + 3
				qui: append using "$path/seccion6_`w2'_imp`imp'.dta"



       }
    }
	save "$path/seccion6_all_imp`imp'.dta", replace
}
}



u  "$path/seccion6_all_imp1.dta", clear
forvalues imp =2(1)5{
    qui: append using "$path/seccion6_all_imp`imp'.dta"
}




//Merge the 3 databases (other sections, section 6 and the ID corsswalk)

merge 1:1 h2 year implicate using "$path/otras_secciones_all.dta", gen(final_mergeee)


merge 1:1 h2 year implicate using "$path/cross_walk_long.dta", gen(final_merge_crosswalk)




order ID h2 year implicate facine3



//Save the final database
save "$path/EFF_${w_min}_${w_max}.dta", replace


//Drop the auxiliar databases
forvalues i = $w_min (3) $w_max {
	erase "$path/cross_walk_ID_`i'.dta"
}


forvalues imp = 1(1)5{

	erase "$path/cross_walk_long_imp`imp'.dta"
}

forvalues imp = 1(1)5{
forvalues i = $w_min (3) $w_max {
	if `i' == 2002{
	
	erase "$path/other_sections_2002_imp`imp'.dta"
	erase "$path/section6_2002_imp`imp'.dta"

}
	if `i'!=2002{
	
	erase "$path/otras_secciones_`i'_imp`imp'.dta"
	erase "$path/seccion6_`i'_imp`imp'.dta"
}
}
}

//comprobaciones
sort ID

gen auxaux=1 if ID!=ID[_n-1]
tab auxaux,m


preserve 
collapse (sum) auxaux, by(ID)
count
tab auxaux,m
restore