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 code			"$root\DATA\CODE\v4_analysis_tests\idb_portal\pearson"
gl out 			"$root\DATA\CODE\v4_analysis_tests\idb_portal\pearson"
gl core 		"$root\DATA\CODE\v4_analysis_tests\idb_portal\core"

gl namefile 	"$out\znonpar_bayley_mil_12mths.xlsx"		

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

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

gl col_vars r_b_st_cog_kd r_b_st_lr_kd r_b_st_le_kd r_b_st_mf_kd r_b_st_mg_kd 							// variables Bayley
gl row_vars r_mil_st_tot_kd																				

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 Bayley-III																					//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)
	tempfile dataset
	save `dataset', replace

//******Bayley vs Milestones: 6-18 months
	u `dataset', clear 																	//open dataset
	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"				// 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)		
		
//****************Bayley vs Milestones: 6-12 months
	u `dataset', clear 																	//open dataset
	keep if b_age_mth>=6 & b_age_mth<=12 
	
	do "$core\pvaluesmat.do"															// generate matrix with p-values
	
	correlate $row_vars $col_vars

	gl typetest "Pearson Correlation $col_test VS $row_test 6 - 12 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	

	
//****************Bayley 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 - 15 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