Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Exporting frequency tables to excel including n=0

    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


  • #2
    You may want to check out fre from SSC.

    Code:
    ssc describe fre
    Code:
    sysuse auto, clear
    fre rep78 if foreign, include(1/5)
    fre foreign, includelabeled
    Res.:

    Code:
    . 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           
    ----------------------------------------------------------------

    Comment


    • #3
      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

      Comment


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

        Code:
        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:

        Click image for larger version

Name:	Capture.PNG
Views:	1
Size:	38.2 KB
ID:	1772462

        Last edited by Andrew Musau; 10 Feb 2025, 11:51.

        Comment

        Working...
        X