// 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 " " }