Putdocx and looping

[Josan Rueda]

Dear list,

I have three variables called Sex (coded as male/female), HTN (yes/no) and Cancer (yes/no).

I want to creat a MS Word table with relative frequencies of all variables within cancer.

My syntax is as follows:

clear
input Sex HTN Cancer n
0 0 0 20
0 1 0 20
0 0 1 15
0 1 1 20
1 0 0 20
1 1 0 20
1 1 1 25
1 0 1 20
end
expand n

label define dsex 0 "Male" 1 "Female"
label values Sex dsex
label define dyn 0 "No" 1 "Yes"
label values HTN Cancer dyn

putdocx clear
putdocx begin
** Two rows (one for each variable), three columns (one for variable name and one for each category within cancer)
putdocx table Table1 = (2,3)
foreach v of varlist Sex HTN {
    forvalues n = 1/2 {
        tabulate `v' Cancer, matcell(`v')
        putdocx table Table1(`n',1)=("`v'")
        putdocx table Table1(`n',2)=(`v'[2,1]*100/(`v'[2,1]+`v'[1,1]))
        putdocx table Table1(`n',3)=(`v'[2,2]*100/(`v'[2,2]+`v'[1,2]))
}

}
putdocx save Table1, replace

This is the table I get. The calculations are correct, but the are only made for the last variable.

HTN	50	56.25
HTN	50	56.25

I think the mistake is in the loop, but after several trials I don't get to identify it.

May anyone give some ideas?

Thank you!

[Daniel Shin]

You're correct that you have an issue in the loop. Play out the scenario:
1) v = "sex", n = 1
2) v = "sex", n = 2
3) v = "HTN", n = 1
4) v = "HTN", n = 2

What you probably want is a counter with the variable:

putdocx table Table1 = (2,3)
local i = 1
foreach v of varlist Sex HTN {
   tabulate `v' Cancer, matcell(`v')
   putdocx table Table1(`i',1)=("`v'")
   putdocx table Table1(`i',2)=(`v'[2,1]*100/(`v'[2,1]+`v'[1,1]))
   putdocx table Table1(`i',3)=(`v'[2,2]*100/(`v'[2,2]+`v'[1,2]))
   local i = `i' + 1
}
putdocx save Table1, replace

Give collect a try:

table (var) (Cancer), stat(fvfrequency Sex HTN) stat(fvpercent Sex HTN) nototals
/*
--------------------------------------------
                            |      Cancer   
                            |     No     Yes
----------------------------+---------------
Sex=Male                    |               
  Factor-variable frequency |     40      35
  Factor-variable percent   |  50.00   43.75
Sex=Female                  |               
  Factor-variable frequency |     40      45
  Factor-variable percent   |  50.00   56.25
HTN=No                      |               
  Factor-variable frequency |     40      35
  Factor-variable percent   |  50.00   43.75
HTN=Yes                     |               
  Factor-variable frequency |     40      45
  Factor-variable percent   |  50.00   56.25
--------------------------------------------
*/
collect style row stack, nobinder spacer
collect label levels result fvfrequency "Freq", modify
collect label levels result fvpercent "%", modify
collect layout (var) (Cancer#result)
/*
Collection: Table
      Rows: var
   Columns: Cancer#result
   Table 1: 6 x 4

---------------------------------------
         |             Cancer          
         |       No             Yes    
         |  Freq       %   Freq       %
---------+-----------------------------
Sex      |                             
  Male   |    40   50.00     35   43.75
  Female |    40   50.00     45   56.25
HTN      |                             
  No     |    40   50.00     35   43.75
  Yes    |    40   50.00     45   56.25
---------------------------------------

*/
collect layout (var[1.Sex 1.HTN]) (Cancer#result[fvpercent])
/*

Collection: Table
      Rows: var[1.Sex 1.HTN]
   Columns: Cancer#result[fvpercent]
   Table 1: 4 x 2

-------------------------
         |      Cancer   
         |     No     Yes
         |      %       %
---------+---------------
Sex      |               
  Female |  50.00   56.25
HTN      |               
  Yes    |  50.00   56.25
-------------------------
*/

putdocx clear
putdocx begin
putdocx collect, table(Table1)
putdocx save Table1, replace

[Josan Rueda]

Hello Daniel,

Both options work fine.

THANK YOU so very much.