*! version 1.06 16May2012 Minh Cong Nguyen
* version 1.06: 16May2012 - adding more statistics for continuous variables. 
* version 1.05 19Nov2011 - basic command

# delimit ;
cap program drop tabmult;
program define tabmult, rclass;
	version 9, missing;
	local version : di "version " string(_caller()) ", missing:";
	syntax [anything] [if] [in] [aw], [cat(varlist) cont(varlist)] by(varlist) [Row Col SCol SRow Missing STATCont(string) save(string) SHeet(string) replace append SAVEMat(string)];
	local cmdline: copy local 0 ;
	marksample touse;	
	if `"`weight'"' != "" {;		
		local wgt `"[`weight'`exp']"';
	};
	
	if ~missing(`"`missing'"') {;
		local missopt missing;
		local mcount = 1;
		local mstr _VX_;
	};
	else {;
		local missopt;
		local mcount = 0;
		local mstr ;
	};
	if ("`row'"!="") + ("`col'"!="") >1 {;
		di in red "choose only one option: row or col. The default is freq";
		exit 198 ;
	};
	local cat  : list uniq cat;
	local cont : list uniq cont;
	local by   : list uniq by;
	if "`:list cat & cont'"!="" {; 
		di as err "Variables `:list cat & cont' cannot be both in the categorial and continuous lists";
		error 198 ;
	};
	local vlist : list cat | cont;
	if "`vlist'"=="" {;
		di as err "There are no variables in the categorial and/or continuous list";
		error 198 ;
	};
	// checking variables;
	foreach var of local cont {;
		qui su `var', meanonly;
		if r(N)==0 local vbad "`vbad' `var'";
	};
	if `: word count `vbad''>0 {;
		dis in ye _n "The following variable(s) have no numeric observations or is/are string variables: `vbad'"; 
		error 109 ;
	};
	if "`:list vlist & by'"!="" { ;
		di as err "Variables `:list vlist & by' cannot be both in the categorial and/or continuous lists and the by list";
		error 198 ;
	};
	if (`"`row'"'==`""' & `"`col'"'==`""') {;
		local opts;
		local weight1;
		local topt count;
		local rla "(count)";
		if ~missing("`statcont'") local topt "`statcont'";		
	};
	else if (`"`row'"'!=`""' & `"`col'"'==`""') {;
		local opts nof row;
		local weight1 `wgt';
		local topt mean;
		local rla "(mean)";
		if ~missing("`statcont'") local topt "`statcont'";		
	};
	else if (`"`row'"'==`""' & `"`col'"'!=`""') {;
		local opts nof col;
		local weight1 `wgt';
		local topt mean;
		local rla "(mean)";
		if ~missing("`statcont'") local topt "`statcont'";		
	};
	if ~missing(`"`scol'"') {;
		tempname __all;
		tempvar  __alll;
		qui gen `__all' = 1 if `touse';
		qui la var `__all' "All";
		qui la def `__alll' 1 "sample";
		qui la val `__all' `__alll';
		local by `"`by' `__all'"';
	};
	// Saving and Excel options;
	if missing("`save'") {;
		local save "stata_tabmult.xml";
		local replace "replace";		
	};
	else {;	
		_getfilename "`save'";
		if `"`r(filename)'"'==`"`save'"' {;
			local save "`c(pwd)'/`save'";
		};
		else {;
			if index(`"`r(filename)'"', ".")==0 {;
				local save `"`save'.xml"';
			};
		};	
		if ~missing("`append'") {;
			capture confirm file "`save'";
			if _rc {;
				local replace "replace";
			};
			else {;
				local append "append";
			};
		};		
		if missing("`append'`replace'") {;
			capture confirm file "`save'";
			if ~_rc {;
				display as error `"Error 602: file `save' already exists"';
				exit 198 ;
			};
			else {;
				local replace "replace";
			};
		};
	};
	local filename = cond(strpos("`save'", "\") + strpos("`save'", "/") > 0, "`save'", "`c(pwd)'\\`save'");
	
	// get sheet options
	local 0 `sheet';
	syntax [anything(name=sheet)] [, COlor(integer -1) noGridlines];

	// check if sheet name is valid
	gettoken a b : sheet, parse(":\/?*[]");

	if ~missing("`b'") | length("`sheet'")>31 Error 198 `"not a valid Excel sheet name"';

	if missing("`sheet'") local sheet "Sheet1";
	
	// Excel options;	
	local excelpath;
	capture local dirs : dir "C:/Program Files (x86)/Microsoft Office" dirs "office*";
	if ~_rc {;
			local x86 " (x86)";
	};
	else {;
			capture local dirs : dir "C:/Program Files/Microsoft Office" dirs "office*";
	};
	foreach d of local dirs {;
			capture confirm file "C:/Program Files`x86'/Microsoft Office/`d'/excel.exe";
			if ~_rc {;
					local excelpath "C:/Program Files`x86'/Microsoft Office/`d'/excel.exe";
					continue, break;
			};
	};
		
	// Check variables;
	preserve;
	local vlist2 : list cat | by;
	local vlist3 : list cont | vlist2;
	foreach v2 of local vlist3 {;
		local varl : variable label `v2';
		local varl : subinstr local varl `"`=char(34)'"' "", all;
		if (`"`varl'"'==`""') la var `v2' "`v2'";		
		capture confirm string variable `v2';
		if !_rc {;
			qui encode `v2' if `touse', gen(`v2'_XXX) label(`v2');
			qui drop `v2';
			qui ren `v2'_XXX `v2';
	        };
	};
	
	// Process the labels for by groups;
	foreach v1 of local by {;
		qui levelsof `v1' if `touse', local(l_`v1');
		local lbl0 : variable label `v1';		
		local rn0 = proper("`lbl0'");
		local bynames "`bynames' "`rn0'"";
		foreach rn of local l_`v1' {;
			local lbl : label (`v1') `rn';
			local lbl = proper("`lbl'");
			local rn1 "`lbl'";
			local cnames "`cnames' "`rn1'"";
		};
		if ~missing(`"`missing'"') local cnames "`cnames' "`missopt'"";		
	};
		
	// Process the labels for cat groups;
	foreach v2 of local cat {;
		qui levelsof `v2' if `touse', local(l_`v2');
		local lbl0 : variable label `v2';
		local lbl0 = proper("`lbl0'");
		local rn0 "_GX_`lbl0'";
		local rnames "`rnames' "`rn0'"";
		foreach rn of local l_`v2' {;
			local lbl : label (`v2') `rn';
			local lbl = proper("`lbl'");
			local rn1 "_VX_`lbl'";			
			local rnames "`rnames' "`rn1'"";
		};
		if ~missing(`"`missing'"') local rnames "`rnames' "`mstr'`missopt'"";
		if ~missing(`"`srow'"')    local rnames "`rnames' "_VX_Total""; 
	};
	
	// Process the labels for cont groups;
	foreach v2 of local cont {;		
		local lbl0 : variable label `v2';
		local lbl0 = proper("`lbl0'");		
		if ("`statcont'"=="") {;
			local rn0 "_GZ_`rla'_`lbl0'";
			local rnames "`rnames' "`rn0'"";
		};
		else {;
			foreach nr of local statcont {;
				local rn0 "_GZ_(`nr')_`lbl0'";
				local rnames "`rnames' "`rn0'"";
			};
		};
	};	
	
	// Process the numbers in cells for cat groups;
	cap mat drop mall2;
	if ("`cat'"!="") {;
		foreach v2 of local cat {;
			cap mat drop mall1;
			foreach v1 of local by {;						
				qui ta `v2' `v1' if `touse' `weight1', `opts' `missopt' matcell(mcell) matrow(mrow) matcol(mcol);
				local strmcoll;
				forv i=1(1)`=colsof(mcol)' {;
					local xbs = mcol[1,`i'];
					local strmcoll "`strmcoll' `xbs'";
				};			
				local strmrow;
				forv i=1(1)`=rowsof(mrow)' {;
					local xbs = mrow[`i',1];
					local strmrow "`strmrow' `xbs'";
				};
				//Process the mcell matrix;			
				local tot2 = 0;
				if ~missing(`"`srow'"') {;
					local misrow = 1;
					mat mrowt = J(1,`=colsof(mcell)',.);
					forv i=1(1)`=colsof(mcell)' {;
						local tot1 = 0;
						forv j=1(1)`=rowsof(mcell)' {;
							local tot1 = `tot1' + mcell[`j',`i'];
						};
						local tot2 = `tot2' + `tot1';
						mat mrowt[1,`i'] = `tot1';
					};
					if (("`col'"=="col") | ("`row'"=="row")) mat mrowt = (100/`tot2')*mrowt;														
				};
				if "`v1'"~="`__all'" {;
					if ("`row'"=="row") {;
						// row options, sum to 100 in rows;				
						forv i=1(1)`=rowsof(mcell)' {;
							local tot = 0;
							forv j=1(1)`=colsof(mcell)' {;
								local tot = `tot' + mcell[`i',`j'];
							};
							forv j=1(1)`=colsof(mcell)' {;
								mat mcell[`i',`j'] = (mcell[`i',`j']/`tot')*100;
							};					
						};
					};			
					if ("`col'"=="col") {;
						// col options, sum to 100 in columns;
						forv i=1(1)`=colsof(mcell)' {;
							local tot = 0;
							forv j=1(1)`=rowsof(mcell)' {;
								local tot = `tot' + mcell[`j',`i'];
							};
							forv j=1(1)`=rowsof(mcell)' {;
								mat mcell[`j',`i'] = (mcell[`j',`i']/`tot')*100;
							};					
						};
					};
				};
				else {;
					if ("`col'"=="col") | ("`row'"=="row") {;
					// scol options, sum to 100 in columns;
						forv i=1(1)`=colsof(mcell)' {;
							local tot = 0;
							forv j=1(1)`=rowsof(mcell)' {;
								local tot = `tot' + mcell[`j',`i'];
							};
							forv j=1(1)`=rowsof(mcell)' {;
								mat mcell[`j',`i'] = (mcell[`j',`i']/`tot')*100;
							};					
						};
					};
				};
				if ~missing(`"`srow'"') mat mcell = mcell \ mrowt;	
				local pos = 1;
				foreach mr of local l_`v1' {;
					local x : list posof "`mr'" in strmcoll;
					if `x'==0 {;					
						if `pos'<=colsof(mcell) {;
							if (`pos'==1) mat mcell = J(rowsof(mcell),1,0), mcell;						
							if (`pos'> 1) mat mcell = mcell[.,1..`=`pos'-1'], J(rowsof(mcell),1,0), mcell[.,`pos'..colsof(mcell)];					
						};
						else if `pos'>colsof(mcell) {;
							mat mcell = mcell[.,1..`=`pos'-1'], J(rowsof(mcell),1,0);
						};					
					};
					local ++ pos;
				};
				// add missing to the output;
				local x : list posof "." in strmcoll;
				if `x'==0 & ~missing(`"`missing'"') {;
					mat mcell = mcell, J(rowsof(mcell),1,0);
				};
				
				local pos = 1;
				foreach mr of local l_`v2' {;
					local x : list posof "`mr'" in strmrow;
					if `x'==0 {;					
						if `pos' <= `=rowsof(mcell)-`misrow'' {;
							if (`pos'==1) mat mcell = J(1,colsof(mcell),0) \ mcell;
							if (`pos'> 1) mat mcell = mcell[1..`=`pos'-1',.] \ J(1,colsof(mcell),0) \ mcell[`pos'..rowsof(mcell),.];						
						};
						else if `pos' > `=rowsof(mcell)-`misrow'' {;
							if ~missing(`"`srow'"') {;
								mat mcell = mcell[1..`=`pos'-1',.] \ J(1,colsof(mcell),0) \ mcell[rowsof(mcell),1..colsof(mcell)];
							};
							else {;						
								mat mcell = mcell[1..`=`pos'-1',.] \ J(1,colsof(mcell),0);
							};
						};
					};
					local ++ pos;
				};
				local x : list posof "." in strmrow;
				if `x'==0 & ~missing(`"`missing'"') {;
					if ~missing(`"`srow'"') {;						
						mat mcell = mcell[1..`=rowsof(mcell)-1',.] \ J(1,colsof(mcell),0) \ mcell[rowsof(mcell),1..colsof(mcell)];
					};
					else {;
						mat mcell = mcell \ J(1,colsof(mcell),0);
					};
				};
				mat mall1 = (nullmat(mall1), mcell);			
			}; // end for by;
			mat rblanks = J(1,colsof(mall1),.a);
			mat mall1 = rblanks \ mall1 ;
			mat mall2 = (nullmat(mall2) \ mall1);
		}; // end for v2;
	};
	
	// Process the numbers in cells in cont groups;
	if ("`cont'"!="") {;
		cap mat drop mallx;
		foreach v1 of local by {;			
			qui tabstat `cont' `weight1' if `touse', by(`v1') stat(`topt') `missopt' save;					
			if ~missing(`"`missing'"') {;
				local send = `:word count `l_`v1'''+1;
			};
			else {;
				local send : word count `l_`v1'';
			};			
			forv s=1(1)`send' {;				
				mat x0 = r(Stat`s');												
				if (`s'==`send') & (x0[1,1]==.) {;
					mat mallx = (nullmat(mallx), J(rowsof(mallx),1,.));				
				};
				else {;					
					mat x1 = vec(r(Stat`s'));
					mat mallx = (nullmat(mallx), x1);				
				};
			};
		};		
		mat mall2 = (nullmat(mall2) \ mallx);
	};
	
	if ~missing("`savemat'") {;
		local rn "`rnames'";
		local rn : subinstr local rn "_GX_" "", all;
		local rn : subinstr local rn "_GZ_" "", all;
		local rn : subinstr local rn "_VX_" "", all;
		mat svm = mall2;
		mat rownames svm = `rn';
		mat colnames svm = `cnames';
		local 0 `savemat';
		syntax name , [replace exit];
		if missing("`replace'") {;
			matrix `namelist'= nullmat(`namelist') \ svm;			
		};
		else {;
			matrix `namelist'= svm;			
		};		
		`exit';				
	};
		
	// Put to dataset;
	local rr  = rowsof(mall2);
	local rc  = colsof(mall2);
	local obs = `rr'+2;
	drop _all;
	qui set obs `obs';	
	qui gen str d_0 = "";	
	local i=0;
	foreach v of local rnames {;
		qui replace d_0 = `"`v'"' in `=`i'+3';
		local ++i;
	};
	local j=1;
	foreach v1 of local cnames {;
		qui gen str d_`j' = `"`v1'"' in 2;
		forv i=1/`rr' {;
			qui replace d_`j' = string(mall2[`i',`j']) in `=`i'+2';
		};
		local ++j;
	};

	//Puting into xml;
	tempfile tmpfile;
	tempname ofile outfile;
	local lsheet "`sheet'";
	if ~missing("`replace'") {;
		local xmlheader  "<?xml version='1.0' encoding='ISO-8859-1'?>
							  <?mso-application progid='Excel.Sheet'?>
							  <Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
										xmlns:o='urn:schemas-microsoft-com:office:office'
										xmlns:x='urn:schemas-microsoft-com:office:excel'
										xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'
										xmlns:html='http://www.w3.org/TR/REC-html40'>";
		file open  `outfile' using "`tmpfile'", write;
		file write `outfile' `"`xmlheader'"' _n;
		local fontstyles "<FontList>01 Times New Roman 10</FontList>";
		file write `outfile' `"`fontstyles'"' _n;
		local styles "<Styles><Style ss:ID='s' />";
		file write `outfile' `"`styles'"';
		local styles "<Style ss:ID='s01_Title'><Alignment ss:Vertical='Center' ss:Horizontal='Center' ss:WrapText='0'/><Font ss:Bold='1' ss:FontName='Times New Roman' ss:Size='10' /></Style>
			<Style ss:ID='s01_Note'><Alignment ss:Vertical='Center' ss:Horizontal='Left' /><Font ss:FontName='Times New Roman' ss:Size='9' /></Style>
			<Style ss:ID='s01_Var'><Alignment ss:Vertical='Center' ss:Horizontal='Left' ss:WrapText='0'/><Font ss:FontName='Times New Roman' ss:Size='10' /></Style>
			<Style ss:ID='s01_Group'><Alignment ss:Vertical='Center' ss:Horizontal='Left' ss:WrapText='0'/><Font ss:Bold='1' ss:FontName='Times New Roman' ss:Size='10' /></Style>
			<Style ss:ID='s01_Freq'><Alignment ss:Vertical='Center' ss:Horizontal='Center' /><Font ss:FontName='Times New Roman' ss:Size='10' /><Borders/><NumberFormat ss:Format='#,##0&quot;&quot;;-#,##0&quot;&quot;' /></Style>
			<Style ss:ID='s01_Numbers'><Alignment ss:Vertical='Center' ss:Horizontal='Center' /><Font ss:FontName='Times New Roman' ss:Size='10' /><Borders/><NumberFormat ss:Format='#,##0.00&quot;&quot;;-#,##0.00&quot;&quot;' /></Style></Styles>";
		file write `outfile' `"`styles'"' _n;
		file write `outfile' "<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>";
		file write `outfile' "<Description>created using tabmult</Description></DocumentProperties>";			
		file write `outfile' `"<Sheetnames>?`lsheet'?</Sheetnames>"' _n;
	};
	else {; // append
		file open `ofile'   using "`save'", read;
		file open `outfile' using "`tmpfile'", write;
		file read  `ofile'  line;                     // header		
		file write `outfile' `"`line'"' _n;
		file read  `ofile'  line;                     // font list		
		file write `outfile' `"`line'"' _n;
		file read  `ofile'  line;                     // styles		
		file write `outfile' `"`line'"' _n;
		file read  `ofile'  line;                     // Description, sheetnames
			if strpos(`"`line'"',"tabmult")==0 {;
				display as error "can not append, existing file was created or changed by some other program";
				exit 603;
			};
		local sheetID=0;
			local sheet  "`lsheet'";
			local dfdfdf : subinstr local line "?`lsheet'?" "", all count(local c);
		while `c'>0 {;
			local ++sheetID;
			local lsheet "`sheet' (`sheetID')";
			local dfdfdf : subinstr local line "?`lsheet'?" "", all count(local c);
		}; //while
		local line : subinstr local line "</Sheetnames>" "`lsheet'?</Sheetnames>";
		file write `outfile' `"`line'"' _n;

		while r(eof)==0 {;
			file read  `ofile'  line;
			if `"`line'"'!="</Workbook>" file write `outfile' `"`line'"' _n;
		}; //for
	};

	file write `outfile' "<Worksheet ss:Name='`lsheet''>" _n;
	if `color'>=0 | ~missing("`gridlines'") {;
        file write `outfile' "<WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>";
        if `color'>=0 file write `outfile' "<TabColorIndex>`color'</TabColorIndex>";
        if ~missing("`gridlines'") file write `outfile' "<DoNotDisplayGridlines/>";
        file write `outfile' "</WorksheetOptions>";
	};
	file write `outfile' "<Table x:FullColumns='1' x:FullRows='1'>" _n;
	file write `outfile' "<Row ss:Index='1'><Cell ss:StyleID='s01_Title' > <Data ss:Type='String'/></Cell>" ;
	tokenize `by';	
	local x=1;
	foreach byn of local bynames {;		
		local k: word count `l_``x''';			
		local ltitle "<Cell ss:StyleID='s01_Title' ss:MergeAcross='`=`k'-1+`mcount'''>		
		              <Data ss:Type='String'>`byn'</Data></Cell>";
		file write `outfile' `"`ltitle'"';		
		local ++x;		
	};
	file write `outfile' "</Row> <Row ss:Index='2'>";
	
	unab vlist : d_*;
	foreach var of local vlist {;
		local datum = `var'[2];
		local xmlrow "`xmlrow' <Cell ss:StyleID='s01_Title' > <Data ss:Type='String'>`datum'</Data></Cell>";
	};
	file write `outfile' `"`xmlrow'"';	
	file write `outfile' "</Row>";
	local  xmlrow "";
	
	forvalues i = 3/`=_N' {;
		file write `outfile' "<Row ss:Index='`i''>";		
		foreach var of local vlist {;
			local datum = `var'[`i'];
			if (`"`datum'"'==".") local datum = "na";	
			if (`"`datum'"'==".a") local datum = "";						
			local dtype = cond(~missing(real(`"`datum'"')), "Number", "String");
			local stype = cond(~missing(real(`"`datum'"')), "s01_Numbers", "s01_Var");
			if (`"`opts'"'=="") & (`"`dtype'"'=="Number") local stype s01_Freq;
			if strpos(`"`datum'"',"_GX_") {;
				local stype s01_Group;
				local datum : subinstr local datum "_GX_" "", all;				
			};
			if strpos(`"`datum'"',"_GZ_") {;
				local stype s01_Var;
				local datum : subinstr local datum "_GZ_" "", all;		
				local datum : subinstr local datum "_" " ", all;		
			};
			if strpos(`"`datum'"',"_VX_") {;
				local stype s01_Var;
				local datum : subinstr local datum "_VX_" "   ", all;				
			};			
			local xmlrow "`xmlrow' <Cell ss:StyleID='`stype'' > <Data ss:Type='`dtype''>`datum'</Data></Cell>";
		};		
		file write `outfile' `"`xmlrow'"';	
		file write `outfile' "</Row>" _n;
		local xmlrow "";
	};
	file write  `outfile' "</Table></Worksheet>" _n;
	file write  `outfile' "</Workbook>";
	file close _all;
	
	capture copy "`tmpfile'" "`save'", replace;
	if _rc {;
		display as error "file can not be saved at this location";
        exit 603;
	};
	display as text "note: results saved to `filename'";
	if "`c(os)'"=="Windows" &  missing("`c(mode)'"){;
			if ~missing(`"`excelpath'"') {;
					display `"{ stata `"winexec "`excelpath'" "`filename'" "': click here}"' _c;
					display as text " to open with Excel";
					if ~missing("`savepath'") global `savepath' "`excelpath'";
			};
			if ~missing(`"`calcpath'"') {;
					display `"{ stata `"winexec "`calcpath'" "`filename'" "': click here}"' _c;
					display as text " to open with Calc";
			};
	};
	restore;
end;