version 12

clear all
macro drop _all
set linesize 80
cap log close
set more off	

*============= PEARSON CORRELATION ===========
gl root 		"C:\DROPBOX\Dropbox\TESTS_COLOMBIA"

gl directory 	"$root\DATA\CLEANDATA\v4_tests\idb_portal\tests_bogota.dta"
gl core			"$root\DATA\CODE\v4_analysis_tests\idb_portal\core"
gl code			"$root\DATA\CODE\v4_analysis_tests\idb_portal\pearson\self"
gl out 			"$root\DATA\CODE\v4_analysis_tests\idb_portal\pearson\self"

gl namefile 	"$out\znonpar_denver_denver_12mths.xlsx"		

log using "$code/denver_denver_pearson_12mths.log", replace 

gl excelsheet "Denver-Denver-12Mths"																	// sheet excel donde se guardan tablas

*NEW -- add correlation Bayley with SES*

gl col_vars r_den_st_lang_kd r_den_st_mf_kd r_den_st_mg_kd r_den_st_soc_kd r_den_st_tot_kd							
gl row_vars r_den_st_lang_kd_bis r_den_st_mf_kd_bis r_den_st_mg_kd_bis r_den_st_soc_kd_bis r_den_st_tot_kd_bis		

gl flag_all flag_all_b_den																			//define flag with all data between tests
gl flag_col b_flag_outlier																			//define col_vars outlier
gl flag_row den_flag_outlier																		//define row_vars outlier

gl col_test Denver-II																					//Title test col_vars
gl row_test Denver-II


********Definir dataset de trabajo*******
	use "$directory", replace 																//open dataset
	keep if $flag_all == 1																	//keep useful data, deleting outliers and missed
	keep if $flag_row == 0
	keep if $flag_col == 0
	keep if battery == 2																//replace with corresponding battery (1=batB, 2=batA)
		
	foreach x of glo col_vars {
		g `x'_bis =`x'
	}
	
	tempfile dataset
	save `dataset', replace

//******Denver vs Denver: 6-42 months
	u `dataset', clear 																	//open dataset
	keep if b_age_mth>=6
	
	do "$core\pvaluesmat.do"
	
	correlate $row_vars $col_vars

	gl typetest "Pearson Correlation $col_test VS $row_test 6 - 42 months"				// define title of table

	matrix A = r(C)																			// matrix with coef. matrix
	matrix B = P																			// matrix with p-values

	do "$core\matstring.do"

	export excel using "$namefile", sheet("$excelsheet") firstrow(varlabels) cell(A1) sheetreplace	// export matrix stored as dataset in Excel

	local numtest = wordcount("$row_vars")+5								// count variables to put the next matrix bellow in the same sheet
																								

*Repeat for each age category by adding age conditions (theses matrices are appended bellow the last one)		
		
//****************Denver vs Denver: 6-18 months
	u `dataset', clear 																	//open dataset
	keep if b_age_mth>=6 & b_age_mth<=18 
	
	do "$core\pvaluesmat.do"															// generate matrix with p-values
	
	correlate $row_vars $col_vars

	gl typetest "Pearson Correlation $col_test VS $row_test 6 - 18 months"				// define title of table

	matrix A = r(C)																		// matrix with coef. matrix
	matrix B = P																		// matrix with p-values (got from pvaluesmat.do)

	do "$core\matstring.do"																

	export excel using "$namefile", sheet("$excelsheet") firstrow(varlabels) cell(A`numtest') sheetmodify	

	local numtest = `numtest'+wordcount("$row_vars")+4															// actualizo numtest para pegar el siguiente	

	
	
//***************Denver vs Denver: 19-30 months
	u `dataset', clear 																	//open dataset
	keep if b_age_mth>=19 & b_age_mth<=30 
	
	do "$core\pvaluesmat.do"
	
	correlate $row_vars $col_vars

	gl typetest "Pearson Correlation $col_test VS $row_test 19 - 30 months"				// define title of table

	matrix A = r(C)																			// matrix with coef. matrix
	matrix B = P																			// matrix with p-values

	do "$core\matstring.do"

	export excel using "$namefile", sheet("$excelsheet") firstrow(varlabels) cell(A`numtest') sheetmodify	

	local numtest = `numtest'+wordcount("$row_vars")+4													// actualizo numtest para pegar el siguiente


//***************Denver vs Denver: 31-42 months
	u `dataset', clear 																	//open dataset
	keep if b_age_mth>=31 & b_age_mth<=42 
	
	do "$core\pvaluesmat.do"
	
	correlate $row_vars $col_vars

	gl typetest "Pearson Correlation $col_test VS $row_test 31 - 42 months"				// define title of table

	matrix A = r(C)																			// matrix with coef. matrix
	matrix B = P																			// matrix with p-values

	do "$core\matstring.do"

	export excel using "$namefile", sheet("$excelsheet") firstrow(varlabels) cell(A`numtest') sheetmodify	

	local numtest = `numtest'+wordcount("$row_vars")+4													// actualizo numtest para pegar el siguiente


	
	
*NEW -- add correlation Denver with SES*
gl col_vars 	edu_mo /*edu_cat_mo*/ wealth_5p wealth_5p_qr estrato fci_act fci_mat zlen stunted b_age_mth female premature 
gl row_vars 	r_den_st_lang_kd r_den_st_mf_kd r_den_st_mg_kd r_den_st_soc_kd


********Definir dataset de trabajo*******

	u `dataset', clear 																	
	keep if b_age_mth>=6
	
	ren fci_play_activities_1 	fci_act
	ren fci_play_materials_1	fci_mat

	foreach x in edu_mo edu_cat_mo {
		bys estrato: egen `x'_med =median(`x')
		replace `x' =`x'_med if `x' ==.
	}
	
	save `dataset', replace

	do "$core\pvaluesmat.do"
	
	correlate $row_vars $col_vars

	gl typetest "Pearson Correlation $col_test VS $row_test 6 - 42 months"				

	matrix A = r(C)																			
	matrix B = P																			

	do "$core\matstring.do"

	export excel using "$namefile", sheet("$excelsheet") firstrow(varlabels) cell(A`numtest') sheetmodify	

	local numtest = `numtest'+wordcount("$row_vars")+5								

	
	* 6 to 18 months
	u `dataset', clear 																
	keep if b_age_mth>=6 & b_age_mth<=18 
	
	do "$core\pvaluesmat.do"															
	
	correlate $row_vars $col_vars

	gl typetest "Pearson Correlation $col_test VS $row_test 6 - 18 months"				

	matrix A = r(C)																		
	matrix B = P																		

	do "$core\matstring.do"																

	export excel using "$namefile", sheet("$excelsheet") firstrow(varlabels) cell(A`numtest') sheetmodify	

	local numtest = `numtest'+wordcount("$row_vars")+5															

	* 19 to 30 months
	u `dataset', clear 																
	keep if b_age_mth>=19 & b_age_mth<=30 
	
	do "$core\pvaluesmat.do"															
	
	correlate $row_vars $col_vars

	gl typetest "Pearson Correlation $col_test VS $row_test 19 - 30 months"				

	matrix A = r(C)																		
	matrix B = P																		

	do "$core\matstring.do"																

	export excel using "$namefile", sheet("$excelsheet") firstrow(varlabels) cell(A`numtest') sheetmodify	

	local numtest = `numtest'+wordcount("$row_vars")+5															

	* 31 to 42 months
	u `dataset', clear 																
	keep if b_age_mth>=31 & b_age_mth<=42 
	
	do "$core\pvaluesmat.do"															
	
	correlate $row_vars $col_vars

	gl typetest "Pearson Correlation $col_test VS $row_test 31 - 42 months"				

	matrix A = r(C)																		
	matrix B = P																		

	do "$core\matstring.do"																

	export excel using "$namefile", sheet("$excelsheet") firstrow(varlabels) cell(A`numtest') sheetmodify	

	local numtest = `numtest'+wordcount("$row_vars")+5