*! version 0.2 22Jul2025
* Programmed by Gustavo Iglésias

program define metaxl_diff, rclass

	version 16

	syntax, [         ///
		Path(str) 	  ///  Directory with meta files (xlsx)
		PATTERN(str)  ///  Pattern for files - based on strmatch patterns
		SAVE(str)     ///  Excel output file
		REPLACE       ///  Replace Excel file if it exists
		BASEfile(str) ///  Name of the base meta file 
		DIFFonly      ///  Shows only rows with differences
		VERBOSE       ///  Shows the progress of the program 
		CHARS         ///  Include analysis for characteristics
		NOTES         ///  Include analysis for notes
		RECursive     ///  Recursively search for meta files
	]
	
	cap which filelist
	if _rc {
		di "{err:This command requires that {bf:filelist} is installed}"
		error 199
	}
	
	if trim("`save'") == "" local save "metadiff"
	cap confirm file "`save'.xlsx"
	if !_rc & "`replace'" == "" {
		di "{err:File {bf:`save'.xlsx} already exists}"
		exit 602
	}
	else {
		cap rm "`save'.xlsx"
	}

	if ("`path'" == "") local path: pwd

	if ("`pattern'" == "") {
		local pattern "*.xlsx"
	}
	else {
		local pattern = "`pattern'" + ".xlsx"
	}
	
	cap putexcel clear
	
	tempfile indexed_data
	
	index_data, fname(`indexed_data') dir(`path') pattern(`pattern') ///
		base(`basefile') history(`save') `verbose' `recursive'
		
	local files `"`r(files)'"'	
	
	* Diff counter 
	global diffs__ = 0
	put_summary, file(`save'.xlsx)
	
	data_history, index_data(`indexed_data') files(`"`files'"') ///
		history(`save') `verbose'

	vars_history, index_data(`indexed_data') files(`"`files'"') ///
		history(`save') `diffonly' `verbose' `chars' `notes'
	return add
	
	vl_history, index_data(`indexed_data') files(`"`files'"') ///
		history(`save') `diffonly' `verbose'
	return add

	di 
	di `"{text:{bf:`save'.xlsx} saved}"'

end


program define index_data, rclass

	/* Indexes the data by meta file. The indexes generated will be of the form 
	base, f1, f2, ... The base index corresponds to the first file after sorting 
	the files by name. This behavior may be changed by specifying option `base`, 
	changing the base file
	*/

	syntax, fname(str) history(str) dir(str) pattern(str) ///
		[base(str) verbose recursive]
	
		if ("`verbose'" == "verbose") {
			di 
			di "Indexing meta files..."
			di
		}

		if ("`recursive'" != "recursive") local norecursive "norecursive"

		tempname frame 
		cap frame drop `frame'
		frame create `frame'
		frame `frame' {
			qui filelist, dir(`dir') pattern(`pattern') `norecursive'
			local obs = _N
			if (`obs' < 2) {
				di "{err:Not enough files to perform the analysis}"
				exit 198
			}
			qui gen _file = ""
			forvalues i = 1/`obs' {
				if c(os) == "Windows" {
					qui replace _file = dirname[`i'] + "\" ///
						+ filename[`i'] in `i'
				}
				else {
					qui replace _file = dirname[`i'] + "/" ///
						+ filename[`i'] in `i'
				}					
			}
			drop dirname filename fsize
			
			// Create index for files
			if ("`base'" != "") {
				tempvar n
				gen `n' = _n 
				qui replace `n' = `n' + 1
				qui replace `n' = 1 if strpos(_file, "`base'")  // Should this be the default behavior?
				sort `n' _file
				drop `n'
			}
			else {
				sort _file
			}
			qui gen index = "base" in 1
			forvalues i = 2/`obs' {
				local j = `i' - 1
				qui replace index = "f`j'" in `i'
			}
			// return local files 
			qui levelsof _file, local(files)
			return local files  `files'
			
			// Dump info into index sheet
			quietly {
				putexcel set "`history'.xlsx", sheet("Index") open modify
				putexcel A2 = "Meta Files Index", bold
				putexcel A4 = "Index", bold underline
				putexcel B4 = "File", bold underline
				
				forvalues i = 1/`obs' {
					local j = `i' + 4
					putexcel A`j' = index[`i']
					putexcel B`j' = _file[`i']
				}
				putexcel save
				// Save temp data
				save `fname', replace
				clear
			}
		}
		frame drop `frame'

end


program define data_history

	/* Creates report data characteristics. Information is gathered from 
	two worksheets in metafiles, namely data_features_gen and data_features_spec
	. In this worksheet we do not report the differences but the actual values 
	in the metadata file
	*/

	syntax, index_data(str) files(str) history(str) [verbose]
	
		if ("`verbose'" == "verbose") {
			di "Working on data characteristics..."
			di
		}
		
	tempfile tempdata
	tempvar _merge
	tempname dataframe
	frame create `dataframe'
	frame `dataframe' {
		foreach file in `files' {
			cap import excel using "`file'", clear first ///
				sheet("data_features_gen")
			* General characteristics
			if !_rc {
				qui keep if substr(Features, 1, 4) == "Data" ///
					| substr(Features, 1, 5) == "Label"
				qui count if substr(Features, 1, 9) == "Data note"
				local notescount = `r(N)'
				qui drop if substr(Features, 1, 9) == "Data note"
				qui replace Content = "Yes" if !missing(Content) & ///
					substr(Features, 1, 10) == "Data Label"
				qui count 
				local last = `r(N)' + 1
				qui set obs `last'
				qui replace Features = "Data notes" in `last'
				qui replace Content = "`notescount'" in `last'
				qui gen _file = "`file'"
				qui cap append using `tempdata'
				qui save `tempdata', replace				
			}

			* Specific characteristics
			cap import excel using "`file'", clear first ///
				sheet("data_features_spec")
			if !_rc {
				qui keep if substr(Features, 1, 6) == "Number"
				qui gen _file = "`file'"
				qui cap append using `tempdata'
				qui save `tempdata', replace				
			}
		}
		qui use `tempdata', clear
		* Merge with the indexed data created with `index_data`
		qui merge m:1 _file using `index_data', gen(`_merge')
		drop `_merge'
		keep Content Features _file index
		sort Features _file
		drop _file
		qui compress
		qui reshape wide Content, i(Features) j(index) string
		rename Content* *
		qui ds 
		foreach var in `r(varlist)' {
			qui replace `var' = "." if missing(`var')
		}
		order *, seq 
		order Features base
		qui export excel using "`history'.xlsx", missing(".") ///
			sheet("Data Characteristics") sheetreplace firstrow(variables)	
	}
	frame drop `dataframe'

end


program define vars_history, rclass

	/* Creates report for variables' worksheet. It compares variables and its 
	labels, values labels, type, etc. for a set of meta files. Uses the indexed 
	data created for each meta file with command `index_data`
	*/

	syntax, index_data(str) files(str) history(str) ///
		[diffonly verbose chars NOTES]

	
	if ("`verbose'" == "verbose") {
		di "{text:Working on variables...}"
		di
	}

	tempfile temp_vars
	tempvar _merge dum 
	tempname vars_frame 
	frame create `vars_frame'
	frame `vars_frame' {
		foreach file in `files' {
			qui import excel using "`file'", clear first ///
				sheet("variables")
			qui gen _file = "`file'"
			qui cap append using `temp_vars'
			qui save `temp_vars', replace
		}
		* Merge with the indexed data created with `index_data`
		qui merge m:1 _file using `index_data', gen(`_merge')
		drop `_merge'
		keep variable label* value_label* type format chars notes index _file
		preserve 
			variables_sheet, history(`history') temp(`temp_vars') ///
				`diffonly' `verbose'
			return add
		restore  
		qui ds
		local vars "`r(varlist)'"
		local vars_exc "variable index _file"
		local vars_chars: list vars - vars_exc
		foreach var in `vars_chars' {
			if ("`var'" == "chars" & "`chars'" != "chars") continue
			if ("`var'" == "notes" & "`notes'" != "notes") continue
			preserve
				variables_chars_sheet, var(`var') history(`history') ///
					temp(`temp_vars') `diffonly' `verbose' 
				return add
			restore
		}
		clear 
	}
	frame drop `vars_frame'

end


program define variables_sheet, rclass

	/* Helper command to save report for variables. This sheet only reports 
	information on whether the variable is present on the metafile or not 
	*/

	syntax, history(str) temp(str) [diffonly verbose]

	tempvar dum 

	keep variable index
	qui gen `dum' = 1
	qui reshape wide `dum', i(variable) j(index) string
	foreach var of varlist `dum'* {
		qui replace `var' = 0 if missing(`var')
	}
	rename `dum'* *
	* Count rows with 1 for every column
	tempvar allones 
	qui gen `allones' = (base == 1) 
	foreach var of varlist f* {
		qui replace `allones' = 0 if `var' != 1
	}
	qui count if !`allones'
	if (`r(N)' == 0 & "`diffonly'" != "diffonly") | (`r(N)') {
		return local variables = `r(N)'
		* Add diffs to summary
		global diffs__ = ${diffs__} + 1
		put_summary, file(`history'.xlsx) worksheet(variables) diffs(`r(N)')
	}
	
	preserve
		drop `allones'
		qui save `temp', replace
	restore
	if ("`diffonly'" == "diffonly") {
		qui drop if `allones'
	}
	drop `allones'

	qui count 
	local obs = `r(N)' 
	
	if `obs' {
		order *, seq
		order variable
		qui export excel using "`history'.xlsx", ///
			sheet("variables") sheetreplace firstrow(variables)	
				
		put_legend, file("`history'.xlsx") sheet(variables) varinc(1) ///
			varexc(0) num_lines(`obs') 
	}
	else {
		di "{err:Worksheet {bf:variables} not exported because of}" ///
			"{err:option {bf:diffonly}}"
	}
	

	if ("`verbose'" == "verbose") {
		di "{text:Worksheet {bf:variables}: done}"
	}

end


program define variables_chars_sheet, rclass

	/* Helper command to save report for variables' characteristics, like label, 
	value label, type, etc. Each sheet will report if the characteristic of the 
	variable is equal or different from the base file. It presents the value in 
	the base file. For the rest of the files, a 0 or 1 is displayed signaling 
	that the value is different or equal to the base file. In the case of type 
	and format, the actual values are displayed when they differ from the base 
	file, otherwise the cell will contain the value "same"
	*/

	syntax, var(str) history(str) temp(str) [diffonly verbose]
	
	tempvar _merge

	keep variable `var' index
	qui reshape wide `var', i(variable) j(index) string
	* Merge with info about variables. If some variable does not appear in a 
	* specific meta file, the cell will be missing (.) in the Excel file
	qui merge 1:1 variable using `temp', gen(`_merge')
	drop `_merge'
	local _type: type `var'base
	if substr("`_type'", 1, 3) == "str" {
		qui replace `var'base = "." if base == 0
	}
	else {
		qui replace `var'base = . if base == 0
	}
	* Generate dummy to flag columns that are all equal (for specific rows)
	tempvar nonequal
	qui gen `nonequal' = 0
	if inlist("`var'", "type", "format") {
		foreach index of varlist f* {
			// Do not include variable format
			if regexm("`index'", "^f[0-9]+") {
				* Compare f# meta file with base meta file 
				qui gen _same`index' = "same" 
				qui replace _same`index' = `var'`index' ///
					if `var'`index' != `var'base 
				qui replace _same`index' =  "." if `index' == 0
				* Add 1 to dummy (flag columns that are equal)
				qui replace `nonequal' = `nonequal' + 1 ///
					if `var'`index' != `var'base
				drop `var'`index' `index'
				rename _same`index' `index'	
			}
		}				
	}
	else if inlist("`var'", "chars", "notes") {
		foreach index of varlist f* {
			qui replace `var'`index' = . if `index' == 0
			drop `index'
			* Add 1 to dummy (flag columns that are equal)
			qui replace `nonequal' = `nonequal' + 1 ///
				if `var'`index' != `var'base
			rename `var'`index' `index'
		}			
	}
	else {
		if strpos("`var'", "value_label") {
			* Dummy to flag variables with no value labels
			tempvar _rownonmiss
			qui gen `_rownonmiss' = !missing(`var'base)
		}
		foreach index of varlist f* {
			if strpos("`var'", "value_label") {
				qui replace `_rownonmiss' = `_rownonmiss' + 1 ///
					if !missing(`var'`index')
			}
			qui gen _same`index' = 0
			qui replace _same`index' = 1 if `var'`index' == `var'base 
			qui replace _same`index' = . if `index' == 0
			* Add 1 to dummy (flag columns that are equal)
			qui replace `nonequal' = `nonequal' + 1 ///
				if `var'`index' != `var'base
			drop `var'`index' `index'
			rename _same`index' `index'
		}
		if strpos("`var'", "value_label") {
			* Drop variables with no value labels
			qui drop if `_rownonmiss' == 0
			drop `_rownonmiss'
		}
	}
	qui count if `nonequal' > 0
	if (`r(N)' == 0 & "`diffonly'" != "diffonly") | (`r(N)') {
		return local `var' = `r(N)'
		* Add diffs to summary
		global diffs__ = ${diffs__} + 1
		put_summary, file(`history'.xlsx) worksheet(`var') diffs(`r(N)')
	}
	if ("`diffonly'" == "diffonly") {
		qui drop if `nonequal' == 0
	}
	drop `nonequal'
	
	qui count 
	local obs = `r(N)'
	
	if (`obs' == 0) {
		di "{err:Worksheet {bf:`var'} not exported because of option} " ///
			"{err:{bf:diffonly}}"
	}
	else {
		drop base
		rename `var'base base 
		order *, seq 
		order variable 
		
		* Label variables
		qui ds 
		foreach varchar in `r(varlist)' {
			if "`varchar'" == "base"  {
				if strpos("`var'", "value_label") {
					label var `varchar' "lblname (base)"
				}
				else {
					label var `varchar' "`var' (base)"
				}
			}
			else {
				label var `varchar' "`varchar'"
			}
		}

		qui export excel using "`history'.xlsx", missing(".") ///
			sheet("`var'") sheetreplace firstrow(varl)	
			
		if inlist("`var'", "chars", "notes") { 
			put_legend, file("`history'.xlsx") sheet(`var') varmiss(.) ///
				num_lines(`obs')
		}
		else if inlist("`var'", "type", "format") { 
			put_legend, file("`history'.xlsx") sheet(`var') varmiss(.) ///
				equal(same) num_lines(`obs')
		}
		else {
			put_legend, file("`history'.xlsx") sheet(`var') varmiss(.) ///
				equal(1) diff(0) num_lines(`obs')
		}	
		
	}

		
	if ("`verbose'" == "verbose") {
		di "{text:Worksheet {bf:`var'}: done}"
	}

end


program define vl_history, rclass

	/* Creates report for value labels worksheet. It compares value labels 
	across meta files. For each value label, a sheet is produced with its name, 
	containing all possible values and labels for all meta files. Then columns 
	base, f1, f2, ... will just have a 0 or a 1 flagging whether that particular 
	value and label are present in the meta file. It is possible to have only 
	missing values for an index if the value label does not exist for that 
	particular meta file. Uses the indexed data created for each meta file 
	with command `index_data`
	*/

	syntax, index_data(str) files(str) history(str) [diffonly verbose]
	
	if ("`verbose'" == "verbose") {
		di 
		di "{text:Working on value labels...}"
		di 
	}

	tempfile temp_value_labels
	tempvar _merge
	tempname vl_frame 
	frame create `vl_frame'
	frame `vl_frame' {
		foreach file in `files' {
			* Find value label sheets in metafiles
			qui import excel "`file'", describe
			local sheetcount = `r(N_worksheet)'
			forval i = 1/`sheetcount' {
				local worksheet_`i' = "`r(worksheet_`i')'"
			}
			forval i = 1/`sheetcount' {
				if substr("`worksheet_`i''", 1, 3) == "vl_" {
					* Import data from value label sheets. Every sheet with 
					* value labels for every meta file will be appended to one 
					* file 
					qui import excel using "`file'", clear ///
						first sheet("`worksheet_`i''")
					qui gen _file = "`file'"
					qui gen _vl = "`worksheet_`i''"
					cap append using `temp_value_labels'
					qui save `temp_value_labels', replace				
				}
			}
		}
		qui count 
		if `r(N)' == 0 {
			di "{err:Warning: no value labels found}"
		}
		else {
			* Merge with indexed data
			qui merge m:1 _file using `index_data', gen(`_merge')
			drop `_merge'
			keep value label index _file _vl
			qui levelsof index, local(index_levels)
			qui levelsof _vl, local(vl_levels)
			foreach vl_level in `vl_levels' {
				* Work on one value label form multiple metafiles 
				preserve
					vl_report, vl_level(`vl_level') index_levels(`index_levels') ///
						history(`history') `diffonly' `verbose'
					return add
				restore
			}			
		}
		clear
	}
	frame drop `vl_frame'
	
end


program define vl_report, rclass

	/* Helper command save report for each value label
	*/

	syntax, vl_level(str) index_levels(str) history(str) [diffonly verbose]

	tempvar dum
	qui keep if _vl == "`vl_level'"	
	drop _file _vl
	qui gen `dum' = 1
	* Flag if a particular value and label are present in the metafiles
	qui reshape wide `dum', i(value label) j(index) string
	rename `dum'* * 
	foreach var in `index_levels' {
		cap confirm var `var'
		if (!_rc) qui replace `var' = 0 if missing(`var')
	}
	foreach index_level in `index_levels' {
		* Make sure that every metafile is in a column. Reshape wide loses that
		* information if the index is not present in the data for a particular 
		* value label
		cap gen `index_level' = .
	}
	order *, seq
	order value label 
	* Count rows with all values equal to one
	tempvar allones 
	qui gen `allones' = (base == 1)
	foreach var of varlist f* {
		qui replace `allones' = 0 if `var' != 1
	}
	qui count if !`allones'
	if (`r(N)' == 0 & "`diffonly'" != "diffonly") | (`r(N)') {
		return local `vl_level' = `r(N)'
		* Add diffs to summary
		global diffs__ = ${diffs__} + 1
		put_summary, file(`history'.xlsx) worksheet(`vl_level') ///
			diffs(`r(N)')
	}
	if ("`diffonly'" == "diffonly") {
		/* Drop columns with all missing values
		foreach var in `index_levels' {
			qui count if !missing(`var')
			if (`r(N)' == 0) qui drop `var'
		}*/
		* Drop if there are no differences
		qui drop if `allones'
	}
	
	drop `allones'
	
	qui count  
	local obs = `r(N)'
	
	* Only export if count is > 0 (diffonly active)
	if `obs' {
		qui export excel using "`history'.xlsx", missing(".")  ///
			sheet("`vl_level'") sheetreplace firstrow(variables)
			
		put_legend, file("`history'.xlsx") sheet(`vl_level') vlmiss(.) ///
				vlinc(1) vlexc(0) num_lines(`obs')
	}
	else {
		di "{err:Worksheet {bf:`vl_level'} not exported because of option}" ///
			"{err: {bf:diffonly}}"
	}
		
	if ("`verbose'" == "verbose") {
		di "{text:Worksheet {bf:`vl_level'}: done}"
	}

end 


program define put_legend

	/* Creates legend for each worksheet present in the analysis
	*/

	syntax, file(str) sheet(str) [varinc(str) varexc(str) vlinc(str) ///
		vlexc(str) diff(str) equal(str) varmiss(str) vlmiss(str)	///
		num_lines(int 0)]
	
	local startline = `num_lines' + 4
	quietly {
		putexcel set "`file'", sheet("`sheet'") open modify
		putexcel A`startline' = "Legend", bold underline
		

		if ("`varexc'" != "") {
			local ++startline
			putexcel A`startline' = "`varexc'"
			putexcel B`startline' = "Variable not in metafile"
		}
		if ("`varinc'" != "") {
			local ++startline
			putexcel A`startline' = "`varinc'"
			putexcel B`startline' = "Variable in metafile"
		}
		if ("`vlexc'" != "") {
			local ++startline
			putexcel A`startline' = "`vlexc'"
			putexcel B`startline' = "Value label not in metafile"
		}
		if ("`vlinc'" != "") {
			local ++startline
			putexcel A`startline' = "`vlinc'"
			putexcel B`startline' = "Value label in metafile"
		}
		if ("`diff'" != "") {
			local ++startline
			putexcel A`startline' = "`diff'"
			putexcel B`startline' = "Different from base"
		}
		if ("`equal'" != "") {
			local ++startline
			putexcel A`startline' = "`equal'"
			putexcel B`startline' = "Equal to base"
		}
		if ("`varmiss'" != "") {
			local ++startline
			putexcel A`startline' = "`varmiss'"
			putexcel B`startline' = "Variable missing"
		}
		if ("`vlmiss'" != "") {
			local ++startline
			putexcel A`startline' = "`vlmiss'"
			putexcel B`startline' = "lblname missing"
		}
		putexcel save
	}

end


program define put_summary

	/* Creates summary with the number of differences for each worksheet in 
	the metafiles analysed
	*/

	syntax, file(str) [worksheet(str) diffs(int 0)]
	
	local startline = `num_lines' + 4
	quietly {
		putexcel set "`file'", sheet("Summary") open modify
		if ${diffs__} == 0 {
			putexcel A2 = "Differences Summary", bold
			putexcel A4 = "Worksheet", bold underline
			putexcel B4 = "Count", bold underline	
		}
		else {
			local cellnum = 4 + ${diffs__}
			putexcel A`cellnum' = "`worksheet'"
			putexcel B`cellnum' = `diffs'
		}
		
		putexcel save
	}

end