// By Tim Plante, MD MHS v1.1 
// March 1, 2021
// timothy.plante@uvm.edu
***************************************************
*************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 quartiles
local assay = word("`anything'", 5) // exposure (eg adiponectin level)
local length = word("`anything'", 6) // rounding

foreach n of numlist `num1' / `num2' {
	capture frame drop table1pweight_jnc`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")  ("Quartile `n'") // first row values (eventually the top row of the excel table)
}
// get Ns
count if `quartname'>=`num1' & `quartname'<=`num2' 
local unweightedN = round(r(N), 1)
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 = round(e(N_pop), 1)
drop stupidtempn
// now get ranges
foreach n of numlist `num1' / `num2' {
	sum `assay' if `quartname'==`n'
	local assaylab: variable label `assay'
	local assaylow = round(r(min),`length')
	local assayhigh= round(r(max),`length')
	frame post `quartname'`n' ("`assaylab' Range:") ("`unweightedN'") ("`weightedN'") ("`assaylow' to `assayhigh'")
}
end


***************************************************
***********continuous variables********************
***************************************************
** normally distributed (mean and SE)
capture program drop table1pweight_contn
program define table1pweight_contn
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 quartiles
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 = round(r(N), 1)
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 = round(e(N_pop), 1)
drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	svy: mean `variable' if `quartname'==`n'
	//matrix list r(table)
	local mean`variable'`n' = round(r(table)[1,1],`length')
	local se`variable'`n' = round(r(table)[2,1],`length')
	local ll`variable'`n' = round(r(table)[5,1],`length')
	local ul`variable'`n' = round(r(table)[6,1],`length')
	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

** skewed/non-normally distribued
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 quartiles
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 = round(r(N), 1)
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 = round(e(N_pop), 1)
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' = r(r1)
	local iqrlow`n' = r(r2)
	local iqrhigh`n' = r(r3)
	local merge `median`n'' (`iqrlow`n'' to `iqrhigh`n'')
//	svy: mean `variable' if `quartname'==`n'
	//matrix list r(table)
//	local mean`variable'`n' = round(r(table)[1,1],`length')
//	local se`variable'`n' = round(r(table)[2,1],`length')
//	local merge `mean`variable'`n'' (`se`variable'`n'') // will merge on the variable name
	frame post `quartname'`n' ("`variablelab'")  ("`unweightedN'") ("`weightedN'") ("`merge'") 
}
end

***************************************************
************binary variables***********************
***************************************************
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 quartiles
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 = round(r(N), 1)
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 = round(e(N_pop), 1)
drop stupidtempn 
foreach n of numlist `num1' / `num2' {
	svy: proportion `variable' if `quartname'==`n'
	local mean`variable'`n' = (round((r(table)[1,2])*100,`length'))
	local se`variable'`n' = round((r(table)[2,2])*100,`length')
	local ll`variable'`n' = round(r(table)[5,2]*100,`length')
	local ul`variable'`n' = round(r(table)[6,2]*100,`length')
	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

***************************************************
***********categorical variables*******************
***************************************************
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 quartiles
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 = round(r(N), 1)
	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 = round(e(N_pop), 1)
	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' = (round((r(table)[1,`y'])*100,`length'))
			local se`variable'`n' = round((r(table)[2,`y'])*100,`length')
			local ll`variable'`n' = round(r(table)[5,`y']*100,`length')
			local ul`variable'`n' = round(r(table)[6,`y']*100,`length')
			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 = round(r(N), 1)
		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 = round(e(N_pop), 1)
		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 quartiles
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. It requires Stata 16.1 or higher."
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."
noisily di " "
noisily di "INSTRUCTIONS:"
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--, 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 quartiles, "
noisily di "   d) name of the variable for the quartile (e.g., adiponectin here), and "
noisily di "   e) rounding for variable (1=at decimal, 0.1=at tenth)"
noisily di "   Example: "
noisily di "   table1pweight_start table1 1 4  quartileadip_pweight_jnc7 adiponectin 1"
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 variables:"
noisily di "   table1pweight_contn -- Continuous, normally distributed"
noisily di "   table1pweight_conts -- Continuous, skewed/non-normally distributed*"
noisily di "   table1pweight_cat -- Categorical"
noisily di "   table1pweight_bin -- Binary/dichotomous"
noisily di "     *note: this command is the only one that really needs the e) pweight variable"
noisily di "      because it uses the _pctile command, which requires the pweight to be"
noisily di "      respecified. All others use Instructions here recommend for each command for simplicity."
noisily di "    Example: table1pweight_cont table1 1 4  quartileadip_pweight_jnc7 age 1"
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 1"
noisily di " "
}