Loop creating table automatically

[Manqing Liu]

Hi -

I am trying to create a huge table with multiple statistical tests for a survey data with many items automatically in STATA 14, my current code is like this:

capture program drop table2

program define table2

use data, clear

global part1 "Q07f Q07h Q07b Q07a Q07e Q07i Q07d Q07g Q07c Q07j Q07k"

disp "Variable" _col(20) "base_curr" _col(40) "treat_curr" _col(60) "base_flex" _col(80) "treat_flex"  ///
_col(100) "DID (95% CI)" _col(120) "or_base (95% CI)" _col(140) "or_flex (95% CI)" _col(157) "p-inter"

foreach var of varlist $part1 {

qui gen dummy`var' = 1 if `var' >= 4
qui replace dummy`var' = 0 if `var' <= 3
qui tab dummy`var' arm if trt == 0 & !missing(`var'), matcell(x)

qui matrix list x

local a = x[2,1]
local b = x[1,1]
local c = `a' + `b'
local d = `a'/`c'
local e = `d'*100
local a1 = x[2,2]
local b1 = x[1,2]
local c1 = `a1' + `b1'
local d1 = `a1'/`c1'
local e1 = `d1'*100

qui tab dummy`var' arm if trt == 1 & !missing(`var'), matcell(y)

qui matrix list y

local f = y[2,1]
local g = y[1,1]
local h = `f' + `g'
local i = `f'/ `g'
local j = `i'*100
local f1 = y[2,2]
local g1 = y[1,2]
local h1 = `f1' + `g1'
local i1 = `f1'/`h1'
local j1 = `i1'*100

qui xi: xtmixed dummy`var' arm##trt || ProgNum:, variance reml

qui matrix M = r(table)

qui matrix list M

local did = M[1,8]
local did2 = `did'*100
local ll = M[5,8]
local ul = M[6,8]
local ll2 = `ll'*100
local ul2 = `ul'*100

qui melogit dummy`var' arm if trt == 0 || ProgNum:, or  

matrix m2 = r(table)

qui matrix list m2

local or1 = m2[1,1]
local ll3 = m2[5,1]
local ul3 = m2[6,1]

qui melogit dummy`var' arm if trt == 1 || ProgNum:, or 

matrix m3 = r(table)

qui matrix list m3

local or2 = m3[1,1]
local ll4 = m3[5,1]
local ul4 = m3[6,1]

qui melogit dummy`var' arm##trt || ProgNum:, or 

matrix m4 = r(table)

qui matrix list m4

local p = m4[4,8]

dis  "`var'" _col(20) %3.0f `a' "/" %3.0f `c' "(" %3.1f `e' ")"  ///
    _col(39) `f' "/" `h' "(" %3.1f `j' ")" _col(60) %3.0f `a1' "/" %3.0f `c1'   ///
    "(" %3.1f `e1' ")" _col(80) %3.0f `f1' "/" %3.0f `h1' "(" %3.1f `j1' ")"   ///
    _col(100) %3.1f `did2' "(" %3.1f `ll2' ","  %3.1f `ul2' ")"  _col(120) %3.2f `or1'  ///
    "(" %3.2f `ll3' "," %3.2f `ul3' ")" _col(140) %3.2f `or2' "(" %3.2f `ll4' ","  %3.2f `ul4' ")" ///
    _col(155) %8.3f `p'
}

end

table2

And it produces neat output like this:

Variable           base_curr           treat_curr          base_flex           treat_flex          DID (95% CI)        or_base (95% CI)    or_flex (95% CI) p-inter
Q07f                59/657(9.0)       54/595(10.0)          73/623(11.7)        92/624(14.7)       -0.2(-6.6,6.2)      1.25(0.88,1.78)     1.18(0.89,1.58)   0.810
Q07h                97/657(14.8)      94/595(18.8)         142/623(22.8)       196/623(31.5)       4.0(-2.8,10.7)      1.44(1.06,1.95)     1.63(1.26,2.12)   0.394
Q07b                15/657(2.3)       25/596(4.4)           27/623(4.3)         36/624(5.8)        -3.5(-9.5,2.4)      1.30(0.92,1.84)     1.05(0.82,1.35)   0.172
Q07a                44/657(6.7)       48/596(8.8)           66/623(10.6)        38/624(6.1)        -7.8(-13.9,-1.6)    1.37(1.02,1.84)     0.92(0.72,1.16)   0.012
Q07e                35/657(5.3)       44/595(8.0)           85/623(13.6)        41/624(6.6)        -10.6(-16.8,-4.4)   1.66(1.22,2.26)     0.94(0.72,1.24)   0.001
Q07i                34/657(5.2)       34/595(6.1)           64/623(10.3)        90/623(14.4)       0.8(-5.5,7.1)       1.35(0.94,1.94)     1.35(1.04,1.77)   0.924
Q07d                78/657(11.9)      70/595(13.3)         112/623(18.0)       140/624(22.4)       1.6(-5.0,8.3)       1.36(0.98,1.90)     1.41(1.09,1.82)   0.806
Q07g               110/657(16.7)      110/595(22.7)        166/623(26.6)       220/623(35.3)       3.6(-3.3,10.4)      1.49(1.10,2.01)     1.67(1.25,2.22)   0.443
Q07c                14/657(2.1)       12/596(2.1)           15/623(2.4)         18/624(2.9)        -2.3(-8.1,3.6)      1.20(0.83,1.74)     1.01(0.76,1.33)   0.370
Q07j                45/657(6.8)       36/595(6.4)           65/623(10.4)        31/623(5.0)        -6.4(-12.6,-0.3)    1.33(0.93,1.90)     0.95(0.74,1.22)   0.035
Q07k                71/657(10.8)      64/595(12.1)          76/623(12.2)        33/624(5.3)        -7.6(-13.9,-1.2)    1.18(0.83,1.68)     0.80(0.60,1.06)   0.018

Because for the second part of the items, the dummy variables should be classified differently (except this other codes stay the same) I don't know how to do that neatly, my current code dose not work, and is very long and inefficient:

capture program drop table2

program define table2

use data, clear

global part1 "Q07f Q07h Q07b Q07a Q07e Q07i Q07d Q07g Q07c Q07j Q07k"
global part2 "Q04a Q04b Q04d Q04f Q04i Q04h Q04c Q04k  Q04j Q05a Q05c Q04l Q05d Q05e Q04m Q05f Q05g Q05h Q04e Q05b Q05j"

disp "Variable" _col(20) "base_curr" _col(40) "treat_curr" _col(60) "base_flex" _col(80) "treat_flex"  ///
_col(100) "DID (95% CI)" _col(120) "or_base (95% CI)" _col(140) "or_flex (95% CI)" _col(157) "p-inter"

foreach var of varlist $part1 {

qui gen dummy`var' = 1 if `var' >= 4
qui replace dummy`var' = 0 if `var' <= 3
qui tab dummy`var' arm if trt == 0 & !missing(`var'), matcell(x)

qui matrix list x

local a = x[2,1]
local b = x[1,1]
local c = `a' + `b'
local d = `a'/`c'
local e = `d'*100
local a1 = x[2,2]
local b1 = x[1,2]
local c1 = `a1' + `b1'
local d1 = `a1'/`c1'
local e1 = `d1'*100

qui tab dummy`var' arm if trt == 1 & !missing(`var'), matcell(y)

qui matrix list y

local f = y[2,1]
local g = y[1,1]
local h = `f' + `g'
local i = `f'/ `g'
local j = `i'*100
local f1 = y[2,2]
local g1 = y[1,2]
local h1 = `f1' + `g1'
local i1 = `f1'/`h1'
local j1 = `i1'*100

qui xi: xtmixed dummy`var' arm##trt || ProgNum:, variance reml

qui matrix M = r(table)

qui matrix list M

local did = M[1,8]
local did2 = `did'*100
local ll = M[5,8]
local ul = M[6,8]
local ll2 = `ll'*100
local ul2 = `ul'*100

qui melogit dummy`var' arm if trt == 0 || ProgNum:, or  

matrix m2 = r(table)

qui matrix list m2

local or1 = m2[1,1]
local ll3 = m2[5,1]
local ul3 = m2[6,1]

qui melogit dummy`var' arm if trt == 1 || ProgNum:, or 

matrix m3 = r(table)

qui matrix list m3

local or2 = m3[1,1]
local ll4 = m3[5,1]
local ul4 = m3[6,1]

qui melogit dummy`var' arm##trt || ProgNum:, or 

matrix m4 = r(table)

qui matrix list m4

local p = m4[4,8]

dis  "`var'" _col(20) %3.0f `a' "/" %3.0f `c' "(" %3.1f `e' ")"  ///
    _col(39) `f' "/" `h' "(" %3.1f `j' ")" _col(60) %3.0f `a1' "/" %3.0f `c1'   ///
    "(" %3.1f `e1' ")" _col(80) %3.0f `f1' "/" %3.0f `h1' "(" %3.1f `j1' ")"   ///
    _col(100) %3.1f `did2' "(" %3.1f `ll2' ","  %3.1f `ul2' ")"  _col(120) %3.2f `or1'  ///
    "(" %3.2f `ll3' "," %3.2f `ul3' ")" _col(140) %3.2f `or2' "(" %3.2f `ll4' ","  %3.2f `ul4' ")" ///
    _col(155) %8.3f `p'
}


foreach var of varlist $part2 {

qui gen dummy`var' = 1 if `var' == 3
qui replace dummy`var' = 0 if `var' <= 3
qui tab dummy`var' arm if trt == 0 & !missing(`var'), matcell(x)

qui matrix list x

local a = x[2,1]
local b = x[1,1]
local c = `a' + `b'
local d = `a'/`c'
local e = `d'*100
local a1 = x[2,2]
local b1 = x[1,2]
local c1 = `a1' + `b1'
local d1 = `a1'/`c1'
local e1 = `d1'*100

qui tab dummy`var' arm if trt == 1 & !missing(`var'), matcell(y)

qui matrix list y

local f = y[2,1]
local g = y[1,1]
local h = `f' + `g'
local i = `f'/ `g'
local j = `i'*100
local f1 = y[2,2]
local g1 = y[1,2]
local h1 = `f1' + `g1'
local i1 = `f1'/`h1'
local j1 = `i1'*100

qui xi: xtmixed dummy`var' arm##trt || ProgNum:, variance reml

qui matrix M = r(table)

qui matrix list M

local did = M[1,8]
local did2 = `did'*100
local ll = M[5,8]
local ul = M[6,8]
local ll2 = `ll'*100
local ul2 = `ul'*100

qui melogit dummy`var' arm if trt == 0 || ProgNum:, or  

matrix m2 = r(table)

qui matrix list m2

local or1 = m2[1,1]
local ll3 = m2[5,1]
local ul3 = m2[6,1]

qui melogit dummy`var' arm if trt == 1 || ProgNum:, or 

matrix m3 = r(table)

qui matrix list m3

local or2 = m3[1,1]
local ll4 = m3[5,1]
local ul4 = m3[6,1]

qui melogit dummy`var' arm##trt || ProgNum:, or 

matrix m4 = r(table)

qui matrix list m4

local p = m4[4,8]

dis  "`var'" _col(20) %3.0f `a' "/" %3.0f `c' "(" %3.1f `e' ")"  ///
    _col(39) `f' "/" `h' "(" %3.1f `j' ")" _col(60) %3.0f `a1' "/" %3.0f `c1'   ///
    "(" %3.1f `e1' ")" _col(80) %3.0f `f1' "/" %3.0f `h1' "(" %3.1f `j1' ")"   ///
    _col(100) %3.1f `did2' "(" %3.1f `ll2' ","  %3.1f `ul2' ")"  _col(120) %3.2f `or1'  ///
    "(" %3.2f `ll3' "," %3.2f `ul3' ")" _col(140) %3.2f `or2' "(" %3.2f `ll4' ","  %3.2f `ul4' ")" ///
    _col(155) %8.3f `p'
}

end

table2

STATA could not display second part of the items and said: dummyQ04a is constant and zero.

Any suggestions?

Thanks!!!

[German Rodriguez]

Your question is not about Mata; you may get more responses if you post in the general forum. It would also help if you state clearly your objective and provide some sample data. Otherwise it is hard to see why dummy Q04a is constant and zero.

I would also advise you to follow a divide and conquer strategy: break your problem into small parts and write a separate program for each. For example the first thing you do for each variable is build a 2x2 table of the dummy by arm for trt==0 and trt==1. You could encapsulate this calculation in its own program.

This is simpler and helps catch errors. For example for trt==0 you divide an entry by the sum (a proportion?), but for trt==1 you divide one by the other (an odds ratio?). Did you intend this?

[Manqing Liu]

Thanks, I will move this question to the general forum. I need to calculate both porportion at baseline and at treatment and the odds ratio comparing two arms.