// By Tim Plante, MD MHS v1.2
// Sept 30, 2021, updated March 31, 2022
// timothy.plante@uvm.edu
// Changes:
// 1.2 - change 95% CI to SE. Fix typo with "tabple1pweight_jnc7" that is now
//       a local macro. "Rounding" now replace with number format command.
// 1.2.1 - used the svy, subpop command for proportions and means as is described here: https://stats.idre.ucla.edu/stata/seminars/survey-data-analysis-with-stata-15/
//			(scroll down to "analysis of subpopulations")
// 1.2.2 - changed continous variable 95% CI so that it's not the estimate of the mean, but acutally the 2.5 and 97.5th percentile
// 1.2.3 - changed _se to _sd for contn, deleted the other se ones because they were silly

***************************************************
*************start*********************************
***************************************************
capture program drop table1pweight_start
program define table1pweight_start
version 16.1
syntax anything

local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local assay = word("`anything'", 5) // exposure (eg adiponectin level)
local length = word("`anything'", 6) // rounding

foreach n of numlist `num1' / `num2' {
	capture frame drop `quartname'`n'
	frame create `quartname'`n' strL varname strL unweightedN strL weightedN  strL estimate`n' // variable names
	frame post `quartname'`n' ("Variable")  ("Unweighted N") ("Weighted N")  ("Quantile `n'") // first row values (eventually the top row of the excel table)
}
// get Ns
count if `quartname'>=`num1' & `quartname'<=`num2' 
local unweightedN = trim("`: display %10.0fc r(N)'") // N is always rounded
// details on this trim and formatting command: https://www.stata.com/statalist/archive/2011-05/msg00269.html
gen stupidtempn = _n
svy: total stupidtempn if `quartname'>=`num1' & `quartname'<=`num2' // just coming up with a silly variable name that will be deleted
local weightedN = trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
drop stupidtempn
// now get ranges
foreach n of numlist `num1' / `num2' {
	sum `assay' if `quartname'==`n'
	local assaylab: variable label `assay'
	local assaylow = trim("`: display `length' r(min)'")
	local assayhigh = trim("`: display `length' r(max)'")
	local quantunwtn = trim("`: display %10.0fc r(N)'")
	gen _tempN = 1
	svy: total _tempN if `quartname'==`n'
	local quantwtn = trim("`: display %10.0fc e(_N_subp)[1,1]'") 
	drop _tempN
	frame post `quartname'`n' ("`assaylab' Range:") (" ") (" ") ("`assaylow' to `assayhigh'")
	frame post `quartname'`n' ("Unweighted N:") ("`unweightedN'") (" ") ("`quantunwtn'")
	frame post `quartname'`n' ("Weighted N:") (" ") ("`weightedN'") ("`quantwtn'")
}
end


***************************************************
***********continuous variables********************
****************normally distributed***************
**************mean and 95% CI**********************
***************************************************
capture program drop table1pweight_contn_95
program define table1pweight_contn_95
version 16.1
syntax anything

local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local variable= word("`anything'", 5) // exposure (eg adiponectin level)
local length = word("`anything'", 6)
// saving variable label details at --help macro--, under "Macro functions for extracting data attributes"
local variablelab: variable label `variable'
// get Ns
count if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // so it won't count if the variable is missing
local unweightedN = trim("`: display %10.0fc r(N)'") // N is always rounded
gen stupidtempn = _n
svy: total stupidtempn if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // just coming up with a silly variable name that will be deleted
local weightedN =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	svy, subpop(if `quartname'==`n'): mean `variable' 
	local mean`variable'`n' = trim("`: display `length' r(table)[1,1]'")
	local se`variable'`n' = trim("`: display `length' r(table)[2,1]'")
	local ll`variable'`n' =  trim("`: display `length' r(table)[5,1]'")
	local ul`variable'`n' =  trim("`: display `length' r(table)[6,1]'")
	local merge `mean`variable'`n'' (95% CI `ll`variable'`n'' to `ul`variable'`n'') // 95% CI variant
//	local merge `mean`variable'`n'' (`se`variable'`n'') // SE variant
	svyset // the weighting is stored as macros after this command
	_pctile `variable' if `quartname'==`n' [`r(wtype)'`r(wexp)'] , p(2.5 97.5)
	local low95`n' = trim("`: display `length' r(r1)'")
	local high95`n' = trim("`: display `length' r(r2)'")
	local merge `mean`variable'`n'' (95% CI `low95`n'', `high95`n'')
	frame post `quartname'`n' ("`variablelab'")  ("`unweightedN'") ("`weightedN'") ("`merge'") 
}
end
***************************************************
***********continuous variables********************
****************normally distributed***************
****************mean and SD***********************
***************************************************
capture program drop table1pweight_contn_sd
program define table1pweight_contn_sd
version 16.1
syntax anything

local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local variable= word("`anything'", 5) // exposure (eg adiponectin level)
local length = word("`anything'", 6)
// saving variable label details at --help macro--, under "Macro functions for extracting data attributes"
local variablelab: variable label `variable'
// get Ns
count if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // so it won't count if the variable is missing
local unweightedN = trim("`: display %10.0fc r(N)'") // N is always rounded
gen stupidtempn = _n
svy: total stupidtempn if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // just coming up with a silly variable name that will be deleted
local weightedN =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	svy, subpop(if `quartname'==`n'): mean `variable'
	local mean`variable'`n' = trim("`: display `length' r(table)[1,1]'")
//	local se`variable'`n' = trim("`: display `length' r(table)[2,1]'")
	local ll`variable'`n' =  trim("`: display `length' r(table)[5,1]'")
	local ul`variable'`n' =  trim("`: display `length' r(table)[6,1]'")
	estat sd 
	local sd`variable'`n' = trim("`: display `length' r(sd)[1,1]'")
//	local merge `mean`variable'`n'' (95% CI `ll`variable'`n'' to `ul`variable'`n'') // 95% CI variant
	local merge `mean`variable'`n'' (`sd`variable'`n'') // SD variant
	frame post `quartname'`n' ("`variablelab'")  ("`unweightedN'") ("`weightedN'") ("`merge'") 
}
end

***************************************************
***********continuous variables********************
*************skewed/non-normally distributed*******
**************median and IQR***********************
***************************************************
capture program drop table1pweight_conts
program define table1pweight_conts
version 16.1
syntax anything

local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local variable= word("`anything'", 5) // exposure (eg adiponectin level)
local length = word("`anything'", 6)
// steal variable label
local variablelab: variable label `variable'
// get Ns
count if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // so it won't count if the variable is missing
local unweightedN = trim("`: display %10.0fc r(N)'") // N is always rounded
gen stupidtempn = _n
svy: total stupidtempn if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // just coming up with a silly variable name that will be deleted
local weightedN =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	svyset // the weighting is stored as macros after this command
	_pctile `variable' if `quartname'==`n' [`r(wtype)'`r(wexp)'] , p(25 50 75)
	local median`n' = trim("`: display `length' r(r2)'")
	local iqrlow`n' = trim("`: display `length' r(r1)'")
	local iqrhigh`n' = trim("`: display `length' r(r3)'")
	local merge `median`n'' (IQR `iqrlow`n'' to `iqrhigh`n'')
	frame post `quartname'`n' ("`variablelab'")  ("`unweightedN'") ("`weightedN'") ("`merge'") 
}
end

***************************************************
************binary variables***********************
************proportion 95% CI**********************
***************************************************
capture program drop table1pweight_bin_95
program define table1pweight_bin_95
version 16.1
syntax anything

local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local variable= word("`anything'", 5) // exposure (eg income level)
local length = word("`anything'", 6)

// saving variable label details at --help macro--, under "Macro functions for extracting data attributes"
local variablelab: variable label `variable'
// get Ns
count if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // so it won't count if the variable is missing
local unweightedN = trim("`: display %10.0fc r(N)'") // N is always rounded
gen stupidtempn = _n
svy: total stupidtempn if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // just coming up with a silly variable name that will be deleted
local weightedN =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	svy, subpop(if `quartname'==`n'): proportion `variable'
	local mean`variable'`n' = trim("`: display `length' ((r(table)[1,2])*100)'") 
//	local se`variable'`n' = trim("`: display `length' ((r(table)[2,2])*100)'") 
	local ll`variable'`n' = trim("`: display `length' ((r(table)[5,2])*100)'") 
	local ul`variable'`n' = trim("`: display `length' ((r(table)[6,2])*100)'") 
	local merge `mean`variable'`n''% (95% CI `ll`variable'`n''% to `ul`variable'`n''%) // 95% CI variant
//	local merge `mean`variable'`n''% (`se`variable'`n'') // SE variant

	frame post `quartname'`n' ("`variablelab'")  ("`unweightedN'") ("`weightedN'") ("`merge'") 
}
end

/*
***************************************************
************binary variables***********************
***************SE**********************************
***************************************************
capture program drop table1pweight_bin_se
program define table1pweight_bin_se
version 16.1
syntax anything

local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local variable= word("`anything'", 5) // exposure (eg income level)
local length = word("`anything'", 6)

// saving variable label details at --help macro--, under "Macro functions for extracting data attributes"
local variablelab: variable label `variable'
// get Ns
count if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // so it won't count if the variable is missing
local unweightedN = trim("`: display %10.0fc r(N)'") // N is always rounded
gen stupidtempn = _n
svy: total stupidtempn if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // just coming up with a silly variable name that will be deleted
local weightedN =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	svy, subpop(if `quartname'==`n'): proportion `variable'
	local mean`variable'`n' = trim("`: display `length' ((r(table)[1,2])*100)'") 
	local se`variable'`n' = trim("`: display `length' ((r(table)[2,2])*100)'") 
	local ll`variable'`n' = trim("`: display `length' ((r(table)[5,2])*100)'") 
	local ul`variable'`n' = trim("`: display `length' ((r(table)[6,2])*100)'") 
//	local merge `mean`variable'`n''% (95% CI `ll`variable'`n''% to `ul`variable'`n''%) // 95% CI variant
	local merge `mean`variable'`n''% (`se`variable'`n'') // SE variant

	frame post `quartname'`n' ("`variablelab'")  ("`unweightedN'") ("`weightedN'") ("`merge'") 
}
end

*/


***************************************************
************binary variables***********************
*************only proportion without 95%ci*********
***************************************************
capture program drop table1pweight_bin
program define table1pweight_bin
version 16.1
syntax anything

local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local variable= word("`anything'", 5) // exposure (eg income level)
local length = word("`anything'", 6)

// saving variable label details at --help macro--, under "Macro functions for extracting data attributes"
local variablelab: variable label `variable'
// get Ns
count if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // so it won't count if the variable is missing
local unweightedN = trim("`: display %10.0fc r(N)'") // N is always rounded
gen stupidtempn = _n
svy: total stupidtempn if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // just coming up with a silly variable name that will be deleted
local weightedN =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	svy, subpop(if `quartname'==`n'): proportion `variable' 
	local mean`variable'`n' = trim("`: display `length' ((r(table)[1,2])*100)'") 
//	local se`variable'`n' = trim("`: display `length' ((r(table)[2,2])*100)'") 
//	local ll`variable'`n' = trim("`: display `length' ((r(table)[5,2])*100)'") 
//	local ul`variable'`n' = trim("`: display `length' ((r(table)[6,2])*100)'") 
//	local merge `mean`variable'`n''% (95% CI `ll`variable'`n''% to `ul`variable'`n''%) // 95% CI variant
//	local merge `mean`variable'`n''% (`se`variable'`n'') // SE variant
	local merge `mean`variable'`n''% 

	frame post `quartname'`n' ("`variablelab'")  ("`unweightedN'") ("`weightedN'") ("`merge'") 
}
end


***************************************************
***********categorical variables*******************
*******************95% ci**************************
***************************************************
capture program drop table1pweight_cat_95
program define table1pweight_cat_95
version 16.1
syntax anything

local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local variable= word("`anything'", 5) // exposure (eg income level)
local length = word("`anything'", 6)

levelsof `variable', local(variableops) // macro with the options for the variable
// saving variable label details at --help macro--, under "Macro functions for extracting data attributes"
local variablelab: variable label `variable'
// N for the overall
	count if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // so it won't count if the variable is missing
	local unweightedNall = trim("`: display %10.0fc r(N)'") // N is always rounded
	gen stupidtempn = _n
	svy: total stupidtempn if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // just coming up with a silly variable name that will be deleted
	local weightedNall =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
	drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	frame post `quartname'`n' ("`variablelab'") ("`unweightedNall'") ("`weightedNall'") (" ")
}
foreach n of numlist `num1' / `num2' {
	local y = 0 // to count
	foreach x of numlist  `variableops' { 
		local rowlab: label (`variable') `x' //grab label of the variable for the row label	
		// need to handle missing variables, this if/else loop does that
		count if `quartname'==`n' & `variable'==`x' 
		local zerocheck = r(N)
		if `zerocheck'==0 {
			local merge 0
		}
		
		else {
			svy, subpop(if `quartname'==`n'): proportion `variable' 
			local y = `y'+1
			
			local mean`variable'`n' = trim("`: display `length' r(table)[1,`y']*100'") 
			local se`variable'`n' = trim("`: display `length' r(table)[2,`y']*100'") 
			local ll`variable'`n' = trim("`: display `length' r(table)[5,`y']*100'") 
			local ul`variable'`n' = trim("`: display `length' r(table)[6,`y']*100'") 
			local merge `mean`variable'`n''% (95% CI `ll`variable'`n''% to `ul`variable'`n''%) // 95% CI variant
		//	local merge `mean`variable'`n''% (`se`variable'`n'') // SE variant
		}
		// get Ns 
		count if (`quartname'>=`num1' & `quartname'<=`num2') & `variable'==`x'  // so it won't count if the variable is missing
		local unweightedN = trim("`: display %10.0fc r(N)'") // N is always rounded
		gen stupidtempn = _n
		svy: total stupidtempn if (`quartname'>=`num1' & `quartname'<=`num2') & `variable'==`x' // just coming up with a silly variable name that will be deleted
		local weightedN =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
		drop stupidtempn 
		
		frame post `quartname'`n' ("  `rowlab'")  ("`unweightedN'") ("`weightedN'") ("`merge'") 
	}
}

end

/*
***************************************************
***********categorical variables*******************
*********************SE****************************
***************************************************
capture program drop table1pweight_cat_se
program define table1pweight_cat_se
version 16.1
syntax anything

local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local variable= word("`anything'", 5) // exposure (eg income level)
local length = word("`anything'", 6)

levelsof `variable', local(variableops) // macro with the options for the variable
// saving variable label details at --help macro--, under "Macro functions for extracting data attributes"
local variablelab: variable label `variable'
// N for the overall
	count if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // so it won't count if the variable is missing
	local unweightedNall = trim("`: display %10.0fc r(N)'") // N is always rounded
	gen stupidtempn = _n
	svy: total stupidtempn if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // just coming up with a silly variable name that will be deleted
	local weightedNall =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
	drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	frame post `quartname'`n' ("`variablelab'") ("`unweightedNall'") ("`weightedNall'") (" ")
}
foreach n of numlist `num1' / `num2' {
	local y = 0 // to count
	foreach x of numlist  `variableops' { 
		local rowlab: label (`variable') `x' //grab label of the variable for the row label	
		// need to handle missing variables, this if/else loop does that
		count if `quartname'==`n' & `variable'==`x' 
		local zerocheck = r(N)
		if `zerocheck'==0 {
			local merge 0
		}
		
		else {
			svy, subpop(if `quartname'==`n'): proportion `variable' 
			local y = `y'+1
			
			local mean`variable'`n' = trim("`: display `length' r(table)[1,`y']*100'") 
			local se`variable'`n' = trim("`: display `length' r(table)[2,`y']*100'") 
			local ll`variable'`n' = trim("`: display `length' r(table)[5,`y']*100'") 
			local ul`variable'`n' = trim("`: display `length' r(table)[6,`y']*100'") 
		//	local merge `mean`variable'`n''% (95% CI `ll`variable'`n''% to `ul`variable'`n''%) // 95% CI variant
			local merge `mean`variable'`n''% (`se`variable'`n'') // SE variant
		}
		// get Ns 
		count if (`quartname'>=`num1' & `quartname'<=`num2') & `variable'==`x'  // so it won't count if the variable is missing
		local unweightedN = trim("`: display %10.0fc r(N)'") // N is always rounded
		gen stupidtempn = _n
		svy: total stupidtempn if (`quartname'>=`num1' & `quartname'<=`num2') & `variable'==`x' // just coming up with a silly variable name that will be deleted
		local weightedN =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
		drop stupidtempn 
		
		frame post `quartname'`n' ("  `rowlab'")  ("`unweightedN'") ("`weightedN'") ("`merge'") 
	}
}

end
*/
***************************************************
***********categorical variables*******************
*************only proportions w/o se or 95% ci*****
***************************************************
capture program drop table1pweight_cat
program define table1pweight_cat
version 16.1
syntax anything

local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local variable= word("`anything'", 5) // exposure (eg income level)
local length = word("`anything'", 6)

levelsof `variable', local(variableops) // macro with the options for the variable
// saving variable label details at --help macro--, under "Macro functions for extracting data attributes"
local variablelab: variable label `variable'
// N for the overall
	count if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // so it won't count if the variable is missing
	local unweightedNall = trim("`: display %10.0fc r(N)'") // N is always rounded
	gen stupidtempn = _n
	svy: total stupidtempn if `quartname'>=`num1' & `quartname'<=`num2' & `variable' <. // just coming up with a silly variable name that will be deleted
	local weightedNall =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
	drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	frame post `quartname'`n' ("`variablelab'") ("`unweightedNall'") ("`weightedNall'") (" ")
}
foreach n of numlist `num1' / `num2' {
	local y = 0 // to count
	foreach x of numlist  `variableops' { 
		local rowlab: label (`variable') `x' //grab label of the variable for the row label	
		// need to handle missing variables, this if/else loop does that
		count if `quartname'==`n' & `variable'==`x' 
		local zerocheck = r(N)
		if `zerocheck'==0 {
			local merge 0
		}
		
		else {
			svy: proportion `variable' if `quartname'==`n'
			local y = `y'+1
			
			local mean`variable'`n' = trim("`: display `length' r(table)[1,`y']*100'") 
			local se`variable'`n' = trim("`: display `length' r(table)[2,`y']*100'") 
			local ll`variable'`n' = trim("`: display `length' r(table)[5,`y']*100'") 
			local ul`variable'`n' = trim("`: display `length' r(table)[6,`y']*100'") 
		//	local merge `mean`variable'`n''% (95% CI `ll`variable'`n''% to `ul`variable'`n''%) // 95% CI variant
		//	local merge `mean`variable'`n''% (`se`variable'`n'') // SE variant
			local merge `mean`variable'`n''% // SE variant

			}
		// get Ns 
		count if (`quartname'>=`num1' & `quartname'<=`num2') & `variable'==`x'  // so it won't count if the variable is missing
		local unweightedN = trim("`: display %10.0fc r(N)'") // N is always rounded
		gen stupidtempn = _n
		svy: total stupidtempn if (`quartname'>=`num1' & `quartname'<=`num2') & `variable'==`x' // just coming up with a silly variable name that will be deleted
		local weightedN =  trim("`: display %10.0fc e(N_pop)'")  // N is always rounded
		drop stupidtempn 
		
		frame post `quartname'`n' ("  `rowlab'")  ("`unweightedN'") ("`weightedN'") ("`merge'") 
	}
}

end


***************************************************
***************closing this************************
***************************************************
capture program drop table1pweight_end
program define table1pweight_end
version 16.1
syntax anything
local tablename = word("`anything'", 1)
local num1 = word("`anything'", 2) // first quantile
local num2 = word("`anything'", 3) // last quantile
local quartname = word("`anything'", 4) // variable name for quantiles
local assay = word("`anything'", 5) // exposure (eg adiponectin level)
local length = word("`anything'", 6) // rounding

frame `quartname'`num1' { // this will run the following command in the specified frame without switching to it.
	export excel varname unweightedN weightedN using "`tablename'.xlsx", replace
}

foreach n of numlist `num1' / `num2' {
	// build loop for alphabet
	//di "`=char(65)'" // details about charnum to align rows in excel:
	// https://blog.stata.com/2017/04/06/creating-excel-tables-with-putexcel-part-3-writing-custom-reports-for-arbitrary-variables/
	//
	local charnum=67+`n' // because now 3 original rows, made this 67 instead of 65
frame `quartname'`n' {
		export excel estimate`n' using "`tablename'.xlsx", sheetmodify cell("`=char(`charnum')'"1)
	}
}
// delete the new frames
foreach n of numlist `num1' / `num2' {
	frame drop `quartname'`n'
}
end

quietly {
noisily di "This do file includes a series of programs that, when used together, will "
noisily di "   generate a table 1 based upon p-weights, and save this table 1 as an "
noisily di "   excel file in your present working directory."
noisily di "   Your present working directory is:"
noisily pwd
noisily di "This current version doesn't work with IF commands, so drop all data that you "
noisily di "   don't want included in this table. It requires Stata 16.1 or higher."
noisily di " "
noisily di "INSTRUCTIONS:"
noisily di "0. Drop observations that you don't want in this table. E.g., if you" 
noisily di "   want to drop everyone who has JNC7-definition hypertension," 
noisily di "   you'd do this:"
noisily di "      drop if sbp >=140 | dbp >=90"
noisily di "   Now you can run the rest of this code."
noisily di "   "
noisily di "1. Before doing anything, make sure that you use the svyset command to "
noisily di "   declare the data as survey data."
noisily di "   Example:"
noisily di "     svyset [pweight=samplingweight]"
noisily di "   "
noisily di "2. Next, make sure that you have used weighting to establish quantiles"
noisily di "   in the data. "
noisily di "   Example: "
noisily di "    xtile quartileadip_pweight_jnc7 =quartadiponectin [pweight=samplingweight], nq(4)"
noisily di " "
noisily di "3. First command is --table1pweight_start-- (which gives the ranges of "
noisily di     "quantiles) followed by "
noisily di "   a) the desired name of the excel file, "
noisily di "   b) range of quantiles (1 4 for quartiles) , "
noisily di "   c) variable name for quantiles, "
noisily di "   d) name of the variable of interest (for _start, that's the  "
noisily di "      continuous variable that makes up the quantiles, adiponectin  "
noisily di "      here, for other commands it will be the variable defining the  "
noisily di "      row like age, sex, race, etc), and "
noisily di "   e) format for printed variable, %10.0f is decimal, %10.1f is at tenth," 
noisily di "      %10.2f is at hundredth, %10.3f is at thousandth"
noisily di "   Example: "
noisily di "   table1pweight_start table1 1 4 quartileadip_pweight_jnc7 adiponectin %10.2f"
noisily di "     "
noisily di "4. Then call each row by the variable type followed by the same options "
noisily di "   as in #3, except d is the variable defining the row."
noisily di " "
noisily di "   OPTIONS for first command, based upon variable type:"
noisily di "   table1pweight_contn_95 -- Continuous, norm distributed, mean and 95% CI"
noisily di "   table1pweight_contn_sd -- Continuous, norm distributed, mean and SD"
noisily di "   table1pweight_conts -- Continuous, skewed/non-norm dist, median and IQR"
noisily di "   table1pweight_bin --    Binary, row proportion"
noisily di "   table1pweight_bin_95 -- Binary, row proportion and 95% CI"
noisily di "   table1pweight_cat --    Categorical, column proportion"
noisily di "   table1pweight_cat_95 -- Categorical, column proportion and 95% CI"
noisily di "    ***THE FOLLOWING _SE is INACTIVE NOW, PLEASE USE _SD VARIANT INSTEAD:"
noisily di "   table1pweight_contn_se -- Continuous, norm distributed, mean and SE"

noisily di "    ***THE FOLLOWING ARE INACTIVE NOW, PLEASE USE _bin or _cat VARIANT INSTEAD:"
noisily di "   table1pweight_bin_se -- Binary, row proportion and SE"
noisily di "   table1pweight_cat_se -- Categorical, column proportion and SE"

noisily di " "
noisily di "    Example: table1pweight_cont table1 1 4  quartileadip_pweight_jnc7 age %10.1f"
noisily di " "
noisily di "  -- Repeat #4 as needed --"
noisily di " "
noisily di "5. Finish using --table1pweight_end-- and the same options as for #3 (although c&d&e are ignored)"
noisily di "   Example: table1pweight_end table1 1 4  quartileadip_pweight_jnc7 adiponectin %10.1f"
noisily di " "
noisily di "Known bugs to fix: "
noisily di "1. Long variable names cause this to stop. For now, rename long variable "
noisily di "   name to something shorter if you get an error."
noisily di "2. Not using tempvars, future iterations will integrate that. This script"
noisily di "   will make some new variables after running because of this. In theory,"
noisily di "   this might create issues if the variable names generated by this script"
noisily di "   are the same as existing variables in your dataset."
noisily di "3. At some point integrate if statements so dropping observations won't"
noisily di "   be required."

}