Frequency table for multiple variables with the same 5 answer categories

[Paul Arnem]

Hi,

I have several variables with 5 possible answers. I would like to generate a table similar to the following and export it to Word or Excel. My dataset looks something like this

* Example generated by -dataex-. For more info, type help dataex
clear
input float(y1 y2 y3 y4)
1 2 2 1
2 1 3 2
3 4 4 1
2 3 5 1
5 4 2 4
end

I would like to create the following table and export it to Word or Excel:

tabm y* , row nofreq

which results in the following table:

It seems rather straightforward and simple to me, but so far I was not able to create this table to be used in a Word file.

Thanks for any help.

[Rich Goldstein]

there are some user-written programs that might help: tabm and mrtab; use -search- to find and download; assuming you are using version 17 of Stata (if not you should tell us what version you are using), you can use the new -collect- and -table- commands

[Emil Alnor]

Just following here as I have the exact same problem. My (unsuccesful) approach was:

putexcel set excelsheet
collect table, command(tabm y*, row nofreq)
putexcel a1 = collect

But apperently -collect table- does not work with -tabm-. ("returns: an expression is required for command tabm because it is an nclass command within option command()") Rich Goldstein do you know how to get -tabm- to work with -collect table- ?

Paul Arnem a solution which does not put them in one table but does put all distributions in Excel is:

putexcel set excelsheet, sheet(desiredsheet)
forval x = 1/4{
    local y = 1 + ((`x'-1)*3)
    
    collect table () y`x'
    putexcel i`y' = collect

...but I am hoping that someone can come up with a better solution.

[Nick Cox]

Emil Alnor didn't cross-reference my reply to his earlier thread. https://www.statalist.org/forums/for...nary-variables

In addition, not every table is the result of some hyper-complicated table call. Sometimes you can help yourself by rearranging the data. Here the task of filling in ... is yours.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(y1 y2 y3 y4)
1 2 2 1
2 1 3 2
3 4 4 1
2 3 5 1
5 4 2 4
end

preserve 
gen id = _n
reshape long y, i(id) j(which)  

table ...   

restore

[Emil Alnor]

Nick Cox I have been thinking about how to fill in the ... in your code, in order to produce a table like the one Paul wants to produce, but can't seem to come up with the solution. Perhaps you could help ?

[Nick Cox]

My posting quite often here shouldn't distract from my own many areas of vast ignorance, which include table export to MS Office and also proficiency in the new table.

After reshape long

table which y

or

table y which 

gets you a basic table and there should be options to get row percents only. If not,

 tabulate

gets you there.

EDIT: There you go; Hemanshu Kumar gave a fine answer.

[Hemanshu Kumar]

Following from #4,

clear
input float(y1 y2 y3 y4)
1 2 2 1
2 1 3 2
3 4 4 1
2 3 5 1
5 4 2 4
end

gen id = _n
reshape long y, i(id) j(which)  

gen variable = "y"+string(which)
rename y values

table (variable) (values), stat(percent)

which produces:

----------------------------------------------------------
         |                      values                    
         |      1       2       3       4       5    Total
---------+------------------------------------------------
variable |                                                
  y1     |   5.00   10.00    5.00            5.00    25.00
  y2     |   5.00    5.00    5.00   10.00            25.00
  y3     |          10.00    5.00    5.00    5.00    25.00
  y4     |  15.00    5.00            5.00            25.00
  Total  |  25.00   30.00   15.00   20.00   10.00   100.00
----------------------------------------------------------

[Hemanshu Kumar]

Sorry, I just realised that the original table using tabm had rows adding up to 100%. To achieve this, change the table command in #7 as follows:

table (variable) (values), stat(percent, across(values))

----------------------------------------------------------
         |                      values                    
         |      1       2       3       4       5    Total
---------+------------------------------------------------
variable |                                                
  y1     |  20.00   40.00   20.00           20.00   100.00
  y2     |  20.00   20.00   20.00   40.00           100.00
  y3     |          40.00   20.00   20.00   20.00   100.00
  y4     |  60.00   20.00           20.00           100.00
  Total  |  25.00   30.00   15.00   20.00   10.00   100.00
----------------------------------------------------------

[Emil Alnor]

Nice, thanks Nick Cox and Hemanshu Kumar !