Exporting frequency tables to excel including n=0

[Janin Gustav]

Dear Stata users,

can someone please help me with following problem?

In my Project we have a survey with mulitple survey waves. Every wave we need to transfer a lot of frenquency-tables to a nicely layoutet word-Dokument. Therefore I need to export the tables to Excel in a first step, than I can use Bookmarks to transfer everything in my Word Layout.
My aim are tables looking something like this:

var_1	Freq.	Percent
1	115	72,33
2	44	27,67
Total	159	100,00

For some questions missings should be included, for others not.
The export to excel actually worked quite fine using "putexcel" and "tab2xl".
The only problem: for the transfer to Word it is important that the same values always appear in the same excel-cells and also observations woth n=0 must be reportet.
Using tab2xl does not show results with n=0. Therefore the locations of all values in the table vary.

I already found out, that I can export the frequencies including n=0 with:
tabcount var_1, v(1/3) zero matrix(var_1_m)
putexcel B150 = matrix(var_1_m), names hcenter

But now, the percentages are missing.

I‘ve never worked with matrices before.
Is it possible, to add a new colmn with the percentages to my matrix? And maybe also the value-labels for orientation?

I am happy about every Idea!
Thank you in advance,
Janin

[Andrew Musau]

You may want to check out fre from SSC.

ssc describe fre

sysuse auto, clear
fre rep78 if foreign, include(1/5)
fre foreign, includelabeled

Res.:

. fre rep78 if foreign, include(1/5)

rep78 -- Repair record 1978
-----------------------------------------------------------
              |      Freq.    Percent      Valid       Cum.
--------------+--------------------------------------------
Valid   1     |          0       0.00       0.00       0.00
        2     |          0       0.00       0.00       0.00
        3     |          3      13.64      14.29      14.29
        4     |          9      40.91      42.86      57.14
        5     |          9      40.91      42.86     100.00
        Total |         21      95.45     100.00           
Missing .     |          1       4.55                      
Total         |         22     100.00                      
-----------------------------------------------------------

. 
. fre foreign, includelabeled

foreign -- Car origin
----------------------------------------------------------------
                   |      Freq.    Percent      Valid       Cum.
-------------------+--------------------------------------------
Valid   0 Domestic |         52      70.27      70.27      70.27
        1 Foreign  |         22      29.73      29.73     100.00
        Total      |         74     100.00     100.00           
----------------------------------------------------------------

[Janin Gustav]

Dear Andrew Musau,

thank you a lot for that very helpful answer!!!
When I type "fre var_1, include(1/5)" I get exactly the table I need!

But how can I export it to Excel that way?
I guess I need "putexcel" or "tab2xl", but I can't figure out the correct spelling.

Greetings,
Janin

[Andrew Musau]

You can export the output as a tab-delimited text file and open this in Excel.

fre var_1 using myfile.xls, replace include(1/5)

Then open myfile.xls using Excel. Don't mind that it throws an error "The file and format of 'myfile.xls' don't match...[.] Do you want to open the file anyway?". Just select "Yes" and you will have your table which you can resave as a proper Excel file. For the example in #2, this is what I get: