Multiple variables in one matrix - to putexcel

[patrick handcock]

Hello folks,

Been trying to figure this one out but keep getting roadblocks. Wondered if anyone else here has any suggestions.

Basically I am trying to send the results of multiple tabulated variables to excel in a usable format (i.e. output the variable names, e(b), e(pct) , e(cumpct) all outputted in the same respective columns). See below for context.

I can get as far as getting Stata to compute all the tables in a similar format - which I currently then copy paste into excel. This works, but is clunky, slow, and error prone. Is it possible to have Stata send this output into excel all in one hit (i.e. starting from a particular cell)?

Thanks in advance

putexcel set "$RESULTS/Tables/Main Table.xlsx", sheet("Descriptives") modify

foreach var of varlist CVD_total  ///
sex edlevel09 social_class smokstat prev_dm_base_dmdrg /*dmdrg*/ antihyp lipid antidep  ///
fh_cvd fh_mi fh_cva fh_ca fh_dm {
    estpost tabulate `var' if incl_ex_prevCVD==1   //ALL (analytic sample)
    
    matrix freq = e(b)
    matrix pct = e(pct)

    matrix mtx_`var' = freq \ pct
    matrix mtx_`var'_long = mtx_`var''

    }
matrix dir

// FROM HERE SOMEHOW PUTEXCEL THE LARGE OUTPUT OF TABLES TO EXCEL IN ONE HIT (STARTING FROM A CERTAIN CELL (e.g. A1)

Example of the output I get in Stata - which I simply copy-paste into excel. Means all the results are aligned and I can work with them systematically.

Bottom of output

Hope I am making sense

[William Lisowski]

Perhaps the user-written tabout command (see net describe tabout, from(http://fmwww.bc.edu/RePEc/bocode/t ) can better do what you seek to accomplish. The following example creates the tabs-separated file gnxl.txt.

. sysuse auto, clear
(1978 Automobile Data)

. capture noisily erase gnxl.txt

. foreach var of varlist foreign rep78 {
  2.     tabout `var' using gnxl.txt, append cells(freq col cum) show(none)
  3.     }
(note: file gnxl.txt not found)

Table output written to: gnxl.txt


Table output written to: gnxl.txt


. type gnxl.txt
Car type        No.     %       %
Domestic        52.0    70.3    70.3
Foreign 22.0    29.7    100.0
Total   74.0    100.0   

Repair Record 1978      No.     %       %
1       2.0     2.9     2.9
2       8.0     11.6    14.5
3       30.0    43.5    58.0
4       18.0    26.1    84.1
5       11.0    15.9    100.0
Total   69.0    100.0   

.

[Weverthon Machado]

If all the matrices have the same columns, you could try to append them, adding some blank rows inbetween to separate the tables. Something like this:

master_matrix = J(1,3,.)

foreach var of varlist x y z {
[create mtx_`var' ]

mat header = J(2,3,.)
mat rownames header =  " "  "`var'"

mat master_matrix = master_matrix \ header \ mtx_`var'
}

And then export master_matrix with putexcel.

[patrick handcock]

Thanks Weverthon Machado - I couldn't seem to get this to run (the first line - master_matrix = J(1,3,.) - to run)? Maybe I misunderstood it?

[Weverthon Machado]

My mistake. The first line should be:

mat master_matrix = J(1,3,.)

[patrick handcock]

Thanks again Weverthon Machado

However, I get: " [ is not a valid command name "

Perhaps due to this line:

[create mtx_`var' ]

?? Sorry if a very simple issue

[Weverthon Machado]

I should have made it clearer. This was example code. You should replace this line with whatever code creates each individual matrix (for vars x, y, z etc), like the code inside the loop on your original post.

[patrick handcock]

Hi again Weverthon Machado - sorry not quite getting this to work.I'm afraid.

Thinking it is quite simple - but wondered if you had further thoughts on where I might be going wrong (keen to get it to work)? With the current code setup I get the error:

last estimates not found

Apologies in advance for taking your time with small stuff.

mat master_matrix = J(1,3,.)

foreach var of varlist ACM  ///
sex edlevel09 social_class smokstat antihyp lipid antidep  ///
fh_cvd fh_mi fh_cva fh_ca fh_dm {
    tabulate `var' if incl_ex_prevCVD==1   //ALL (analytic sample)

    matrix freq = e(b)
    matrix pct = e(pct)

    matrix mtx_`var' = freq \ pct
    matrix mtx_`var'_long = mtx_`var''    
    
mat header = J(2,3,.)
mat rownames header =  " "  "`var'"

mat master_matrix = master_matrix \ header \ mtx_`var'
}

[William Lisowski]

Carefully compare the code inside the loop in your original post with the same code in post #6. At minimum you have neglected to include estpost in the code in your loop in post #6, and as a consequence there are no estimates posted, as the error message informs you.

[patrick handcock]

Many thanks.

Apologies I had removed estpost as I thought this was a different approach to using putexcel at the end. Have added estpost back in, but now I get a "conformability error" - unsure why, as the tables all have the same amount of columns should all be the same (i.e. e(b) e(pct) e(cumpct)), just different numbers of rows?

ACM	e(b)	e(pct)	e(cumpct)
0	5173	92.97268	92.97268
1	391	7.027318	100
Total	5564	100
sex	e(b)	e(pct)	e(cumpct)
Male	2310	41.51689	41.51689
Female	3254	58.48311	100
Total	5564	100
edlevel09	e(b)	e(pct)	e(cumpct)
0	1299	23.34651	23.34651
1	700	12.58088	35.92739
2	2502	44.96765	80.89504
3	1063	19.10496	100
Total	5564	100

[William Lisowski]

This command seems problematiic to me.

mat master_matrix = master_matrix \ header \ mtx_`var'

Perhaps it should be

mat master_matrix = master_matrix \ header \ mtx_`var'_long

[patrick handcock]

Thanks William Lisowski - I unfortunately still get the "conformability error" following the below. Still a bit unsure why, since the variables are all categorical (as above examples). Perhaps something to do with the

mat header

part?

mat master_matrix = J(1,3,.)

foreach var of varlist ACM  ///
sex edlevel09 social_class smokstat prev_dm_base_dmdrg /*dmdrg*/ antihyp lipid antidep  ///
fh_cvd fh_mi fh_cva fh_ca fh_dm {
    estpost tabulate `var' if incl_ex_prevCVD==1   //ALL (analytic sample)

    matrix freq = e(b)
    matrix pct = e(pct)

    matrix mtx_`var' = freq \ pct
    matrix mtx_`var'_long = mtx_`var''    
    
mat header = J(2,3,.)
mat rownames header =  " "  "`var'"

mat master_matrix = master_matrix \ header \ mtx_`var'_long
}

[William Lisowski]

I'm giving up here. The technique I showed in post #2 represents the approach I recommend. The output is a tab delimited text file that can be opened in Excel.

[patrick handcock]

Thanks William Lisowski - no worries! The main reason I am pursuing a estpost and putexcel option is that I can then send the Table to specific columns within a spreadsheet that has further links within it and overwrites previous data (without needing to generate a whole new sheet) if code is updated, include variable changes, etc.

The tabout command works, but seems to need to generate a brand new spreadsheet in a specific xls. format each time (making updates to output a bit of a pain).

[Weverthon Machado]

Here's what I think it's happening:

1. William Lisowski was right. It seems that you should use mtx_`var'_long instead of mtx_`var';

2. While looking at the output for matrix dir in your first post, I thought the matrices of interest were mtx_`var' because of the indices. See, for example, the ones for fh_dm. mtx_fh_dm has 2 rows and 3 columns. I presumed this would be [Yes, No] x [e(b), e(pct), e(cumpct)], that is, without the "Total" row. The long version is the transpose, of course. So you would want to append the original one, with [e(b), e(pct), e(cumpct)] as the columns;

3. But here's the thing: I guess you are confusing the estpost output with the matrices you create. Those matrices do not include e(cumpct). So, if I undestand well, mat_fh_dm is actually [e(b), e(pct)]x[Yes, No, Total]. So you indeed want to use the transpose, the "long" version with [Yes, No, Total] x [e(b), e(pct)]. Then, all the matrices you want to join actually have 2, not 3 columns;

4. This means that the header matrix, that I suggested as a separator, should have 2, not 3 columns. Thus, it should be created as:

mat header = J(2,2,.)