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_mil_mil_12mths.xlsx"		

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

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

*NEW -- add correlation Bayley with SES*

gl col_vars r_mil_st_tot_kd																				
gl row_vars r_mil_st_tot_kd_bis																				

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

gl col_test Milestones																				//Title test col_vars
gl row_test Milestones

********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 == 1																//replace with corresponding battery (1=batB, 2=batA)
		
	foreach x of glo col_vars {
		g `x'_bis =`x'
	}
	
	tempfile dataset
	save `dataset', replace

//******Milestones vs Milestones: 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)		
		
//****************Milestones vs Milestones: 6-15 months
	u `dataset', clear 																	//open dataset
	keep if b_age_mth>=6 & b_age_mth<=15 
	
	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	

	
*NEW -- add correlation WHO-Motor 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_mil_st_tot_kd_bis																				


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

	u `dataset', clear 																	
	keep if b_age_mth>=6 & b_age_mth<=15 
	
	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' ==.
	}
	
	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