*version 1.7
*Jack Jerome
*2015.12.30
program define tabex
version 13
syntax [varlist] [if] [using/] , [reset] [cname(string)]
tokenize `varlist'

*not specifying a variable specifies all variables so use `3' here as a proxy for no variables specified
if "`3'" != "" & "`reset'" != "" {
	global count = 0
	display "tabex counter has been reset"
		exit
}
*return error if 0 or >2 variables are specified and reset option is not included
if "`3'" != "" & "`reset'" == "" {
	di as err ///
				"{p}specify up to two variables, or use [,reset] option to reset tabex; " ///
				"{p_end}"
				exit 198
}
*return error if a variable to sum is included but it is not numeric
if "`2'" != ""{ capture confirm numeric var `2'
			if _rc {
				di as err ///
				"{p}second variable is not numeric; " ///
				"cannot sum{p_end}"
				exit 7
			}
			}
*
*Increase global count by 1 for each use of tabex command
global count = $count + 1

*map capital letter to numbers for use in putexcel later
if $count <51 {
loc cn A C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY

	loc choice = $count
	loc which : word `choice' of `cn'
	local col = "`which'"
		}
*
else
if $count >=51 {
di as err	"{p}You have reached the maximum number of tabex (50).  Use the [,reset] option to start a new set and don't forget to change or rename your output file!" ///
					"{p_end}"
		exit 198
}
if "`cname'" == "" local cname "`col'"
*display "`col'"  //just for testing
*display `"`using'"' //just for testing
if `"`using'"' == `""' local using tabex.xlsx
*display `"`using'"' //just for testing
*display "`cname'"  //just for testing

*if a second variable is not specified, do a freq (basic tab) by `1' based on `if'
if "`2'"==""{
tempvar col_tc
quietly gen `col_tc'=1 `if'
	tempvar tot
	by `1', sort: egen `tot'=total(`col_tc')
	tempvar gv
	by  `1', sort: gen `gv'=_n
	sort `1'
			if "`col'"=="A"{
			quietly export excel `1' `tot' using "`using'" if `gv'==1, sheetmodify cell(`col'2)
			quietly putexcel A1=("`1'") using "`using'", modify
			quietly putexcel B1=("`cname'") using "`using'", modify
			}
			else{
			quietly export excel `tot' using "`using'" if `gv'==1, sheetmodify cell(`col'2)
			quietly putexcel `col'1=("`cname'") using "`using'", modify
			}
	*
	}
	*
*if a second variable is specified, do a tabstat(sum) by `1' based on `if'
if "`2'" != ""{
tempvar tot
quietly by `1', sort: egen `tot'=total(`2') `if'
tempvar tot_2
quietly by `1', sort: egen `tot_2'=max(`tot')
quietly replace `tot_2'=0 if `tot_2'==.

tempvar gv
	by  `1', sort: gen `gv'=_n
	sort `1'
			if "`col'"=="A"{
			quietly export excel `1' `tot_2' using "`using'" if `gv'==1, sheetmodify cell(`col'2)
			quietly putexcel A1=("`1'") using "`using'", modify
			quietly putexcel B1=("`cname'") using "`using'", modify
			}
			else{
			quietly export excel `tot_2' using "`using'" if `gv'==1, sheetmodify cell(`col'2)
			quietly putexcel `col'1=("`cname'") using "`using'", modify
			}
	*
	}
	*
	*display name of file saved and the column name specified
	display ""
if "`using'"=="tabex.xlsx"{
display "tab `cname' saved to file:  `c(pwd)'\tabex.xlsx"
}
else{
display "tab `cname' saved to file:  `using'"
}

*reset global macro if reset option is selected
if "`reset'" != ""{
global count = 0
display "tabex counter has been reset"
}
end