Table of multiple choice questions (mrtab) to excel with putexcel

[Marcel Beaufils]

Hello everybody,

I want to export results which I created with the mrtab command into excel. So that I have one well structured table with all variables in it. Most of the variables I work with and use for the mrtab command are just coded with quoted (1) or not-quoted (0). The table displays only the results of the quoted values. And all variables are part of an multiple choice question (for example "choose up to three answers").

But I haven't found any manuals or advises that gives me an idea how to start building my putexcel command. In the picture you can see how the table looks like and that's how I want to export it into excel.

Do you have any idea? Every try rebuilding the putexcel command I use for exporting normal tables or crosstables (tab command) gives me the same error: option matcell() not allowed r (198)

Thank you very much for your help.

Sincerely,

Marcel

[Kevin Crow (StataCorp)]

You must look at the returned results to see what things are left behind for you to build your table in Excel from.

. use http://fmwww.bc.edu/RePEc/bocode/d/drugs.dta
(1997 Survey Data on Swiss Drug Addicts)

. mrtab inco1-inco7, include title(Sources of income) width(24)

                               |             Percent of     Percent
             Sources of income |  Frequency   responses    of cases
-------------------------------+-----------------------------------
inco1          private support |        226       12.83       23.25
             (partner, family, |
                      friends) |
inco2           public support |        607       34.47       62.45
      (unemployment insurance, |
              social benefits) |
inco3             drug dealing |        293       16.64       30.14
inco4    housebreaking, theft, |         50        2.84        5.14
                       robbery |
inco5             prostitution |         82        4.66        8.44
inco6       "mischeln"/begging |        151        8.57       15.53
inco7         legal occupation |        352       19.99       36.21
-------------------------------+-----------------------------------
                         Total |       1761      100.00      181.17

Valid cases:        972
Missing cases:        0

. return list

scalars:
                  r(N) =  972
             r(N_miss) =  0
                  r(r) =  7

macros:
               r(list) : ""private support (partner, family, friends)" "publ.."
               r(type) : "numeric"

matrices:
          r(responses) :  7 x 1

Once you know what is left behind you can then start building your table in Excel using putexcel. For example,

matrix a = r(responses)
local labs = `"`r(list)'"'
local vcase = `r(N)'
local miss = `r(N_miss)'
putexcel set example.xlsx, replace keepcellformat
putexcel (B9)=formula("SUM(B2:B8)")
putexcel (A1:A9)=border("right", "thin")
putexcel (A1:D1)=border("bottom", "thin")
putexcel (A1:D1)=halign("center")
putexcel (A2:A9)=halign("right")
putexcel (A1)=("Sources of income")
putexcel (B1)=("Frequency")
putexcel (C1)=("Percent of responses")
putexcel (D1)=("Percent of cases")
putexcel (A9)=("Total")
putexcel (A11)=("Valid cases:")
putexcel (A12)=("Missing cases:")
putexcel (A9:D9)=border("top", "thin")
putexcel (B2)=matrix(a)
putexcel (B11)=(`vcase')
putexcel (B12)=(`miss')

local i = 2

foreach lab of local labs {
    putexcel A`i'=(`"`lab'"')
    local i = `i' + 1
}

[Kath Shelley]

Kevin, this is very, very helpful code -- thank you for sharing. I have a couple follow up questions related to exporting mrtab results using putexcel.

1) I'd like to sum the total number of responses and write that to Excel, but when I try to use Excel function formulas with putexcel (per your 6th line of code), I get the following error even though the expression is enclosed: formula: expression must be enclosed in ()

2) I am having difficulty using putexcel to export the "percent of responses" and "percent of cases" columns of mrtab output. Naive attempts at dividing the matrix of r(responses) by r(N) were unsuccessful and I'm not sure where to go from here.

Thanks for any insights -
Kath

[Kabira Namit]

Hi Marcel and Kath,

I think this code may help!

clear all    
set more off
capture log close
set linesize 140

* Download an example dataset

use "https://www.dropbox.com/s/i8wk1qaiqluih53/Betel%20Nut%20Use%20%28PNG%29.dta?dl=1"

* (Betel Nut Consumption in Papua New Guinea, Collected in May 2018)

* Download and run the multiple response tab function

ssc install mrtab
mrtab betelnutmum - betelnutother_relative, include title(Betel Nut Use in PNG) nonames
return list

* Copy and paste the subsequent code after each mrtab command

* Please change the description of the table title each time you use the code (see next line). 

local title     `"This is my title"'

* Oh yes, you may want to change the name of the excel document once for an entire dataset. 

local excel     `"example_sheet"'

* Set other locals (labels, valid cases and missing) 

local labs = `"`r(list)'"'
local valid_cases = `r(N)'
local missing = `r(N_miss)'

* Generate matrices

matrix a = r(responses)

mata : st_matrix("freq", colsum(st_matrix("a")))
local frequency = freq[1,1]

matrix b = matrix(a) * (1/`frequency') * 100

matrix c = matrix(a) * (1/r(N)) * 100

* The sum function needs to appear below the frequencies

        * Set the line for where the total will appear
        local d = r(r) + 3
        
        * Set the line for where the total will appear
        local e = r(r) + 2

* Set excel sheet 

putexcel set `excel', sheet("`title'", replace) replace 

** Formatting

putexcel A1:A50, hcenter border(right) overwritefmt
putexcel B1:B50, hcenter overwritefmt
putexcel C1:C50, nformat("number_sep_d2") overwritefmt
putexcel D1:D50, nformat("number_sep_d2") overwritefmt

putexcel (A1:D1), merge hcenter vcenter
putexcel (A1)=("`title'")
putexcel (A2)=("Observations")
putexcel (B2)=("Frequency")
putexcel (C2)=("Percent of responses")
putexcel (D2)=("Percent of cases")
putexcel (A`d')=("Total")

* Locations for 'valid cases' and 'missing observations'

        * Local f sets the line for where the valid cases will go
        local f = r(r) + 5

        * Local g sets the number of lines in the missing cases will go
        local g = r(r) + 6
        
putexcel (A`f')=("Valid cases:")
putexcel (A`g')=("Missing cases:")
putexcel (B`f')=(`valid_cases')
putexcel (B`g')=(`missing')

* Exporting the matrices to Excel

putexcel (B3)=matrix(a)
putexcel (C3)=matrix(b)
putexcel (D3)=matrix(c)

* Placing the totals in the row where the observations end
putexcel (B`d')= `frequency' 
putexcel (C`d')= formula("SUM(C3:C`e')")
putexcel (D`d')= formula("SUM(D3:D`e')")
putexcel (B`d':D`d'), border("top", "double") 

* Finally, adding var labels 

local i = 3
foreach lab of local labs {
    putexcel A`i'=(`"`lab'"')
    local i = `i' + 1
}

[Attaullah Shah]

Users of asdocx can export mrtab tables to Word, Excel, LaTeX, or HTML.

* Load drugs data from the web
use http://fmwww.bc.edu/RePEc/bocode/d/drugs.dta, clear

* run mrtab with asdocx and export 
asdocx mrtab inco1-inco7 if city != 1, include cnames(Sources of income) ///
           title(Table: Multiple responses)  replace nonames

[Mohsin Hossain]

Hi Marcel and Kath,

I think this code may help!

clear all
set more off
capture log close
set linesize 140

* Download an example dataset

use "https://www.dropbox.com/s/i8wk1qaiqluih53/Betel%20Nut%20Use%20%28PNG%29.dta?dl=1"

* (Betel Nut Consumption in Papua New Guinea, Collected in May 2018)

* Download and run the multiple response tab function

ssc install mrtab
mrtab betelnutmum - betelnutother_relative, include title(Betel Nut Use in PNG) nonames
return list

* Copy and paste the subsequent code after each mrtab command

* Please change the description of the table title each time you use the code (see next line).

local title `"This is my title"'

* Oh yes, you may want to change the name of the excel document once for an entire dataset.

local excel `"example_sheet"'

* Set other locals (labels, valid cases and missing)

local labs = `"`r(list)'"'
local valid_cases = `r(N)'
local missing = `r(N_miss)'

* Generate matrices

matrix a = r(responses)

mata : st_matrix("freq", colsum(st_matrix("a")))
local frequency = freq[1,1]

matrix b = matrix(a) * (1/`frequency') * 100

matrix c = matrix(a) * (1/r(N)) * 100

* The sum function needs to appear below the frequencies

* Set the line for where the total will appear
local d = r(r) + 3

* Set the line for where the total will appear
local e = r(r) + 2

* Set excel sheet

putexcel set `excel', sheet("`title'", replace) replace

** Formatting

putexcel A1:A50, hcenter border(right) overwritefmt
putexcel B1:B50, hcenter overwritefmt
putexcel C1:C50, nformat("number_sep_d2") overwritefmt
putexcel D1:D50, nformat("number_sep_d2") overwritefmt

putexcel (A1:D1), merge hcenter vcenter
putexcel (A1)=("`title'")
putexcel (A2)=("Observations")
putexcel (B2)=("Frequency")
putexcel (C2)=("Percent of responses")
putexcel (D2)=("Percent of cases")
putexcel (A`d')=("Total")

* Locations for 'valid cases' and 'missing observations'

* Local f sets the line for where the valid cases will go
local f = r(r) + 5

* Local g sets the number of lines in the missing cases will go
local g = r(r) + 6

putexcel (A`f')=("Valid cases:")
putexcel (A`g')=("Missing cases:")
putexcel (B`f')=(`valid_cases')
putexcel (B`g')=(`missing')

* Exporting the matrices to Excel

putexcel (B3)=matrix(a)
putexcel (C3)=matrix(b)
putexcel (D3)=matrix(c)

* Placing the totals in the row where the observations end
putexcel (B`d')= `frequency'
putexcel (C`d')= formula("SUM(C3:C`e')")
putexcel (D`d')= formula("SUM(D3:D`e')")
putexcel (B`d':D`d'), border("top", "double")

* Finally, adding var labels

local i = 3
foreach lab of local labs {
putexcel A`i'=(`"`lab'"')
local i = `i' + 1
}

Hi Kabira and everyone,

Is it possible to give me the revised code of putexcel using your example dataset if I want to export it by two way variable such as by including by(treat) in martab? I have tried but unable to do it.

gen random= runiform()
gen treat= 0 if random >=.50
replace treat= 1 if treat !=0
label define treat 0 "Control" 1 "Treat"
label values treat treat

* Download and run the multiple response tab function

*ssc install mrtab
mrtab betelnutmum - betelnutother_relative, by(treat) include nofreq col title(Betel Nut Use in PNG) nonames width(54)
return list

I want the output like this in excel.

Thanks.