Announcement

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

  • Exporting tabulation from stata to excel including a column with and another without value labels

    Hi All,

    I'd like to export a simple descriptive frequency table from stata to excel. My variable is 'state', a numeric float. So far I have been using the putexcel command with the following code:

    Code:
    putexcel set myfile, replace                    
    tab2xl state using myfile, col(1) row(1)
    putexcel close
    This gets me the following excel table:
    Click image for larger version

Name:	Screen Shot 2020-03-26 at 11.20.07.png
Views:	1
Size:	103.9 KB
ID:	1542990



    I can't seem to amend the command to get an extra column between 'state' and 'Freq' which includes the non-label numeric values of the variable 'state'. Any suggestions?

  • #2
    You will increase your chances of useful answer by following the FAQ on asking questions – provide Stata code in code delimiters which you do, readable Stata output, and sample data using dataex. With the sample data someone might be tempted into coding this for you.

    It's not clear what the nonnumeric values for the variable state means. If it's just a number associated with each state, you could use list to list the state name and number, then copy and paste it into Excel (use copy-table). There is probably a more sophisticated way to do this, but this would get the job done.

    Comment


    • #3
      A thought I had was to use the -numlabel- built-in command, which will insert the numerical code for a value at the beginning of a value label. After putting the table into Excel, one could use Excel functions to pull the number into another column.

      Code:
      sysuse auto2
      desc rep78
      tab rep78
      numlabel repair, add
      tab rep78
      
      /*
           Repair |
       Record 1978 |      Freq.     Percent        Cum.
      -------------+-----------------------------------
           1. Poor |          2        2.90        2.90
           2. Fair |          8       11.59       14.49
        3. Average |         30       43.48       57.97
           4. Good |         18       26.09       84.06
      5. Excellent |         11       15.94      100.00
      -------------+-----------------------------------
             Total |         69      100.00
      */

      Comment


      • #4
        Phil Bromiley, thank you, I will work on my posting to make it easier for others to answer. By 'non-label numeric value' I meant the number attached to each unique value the variable 'state' can take. There are 51 such unique values in total -- as many as there are states in the United States, plus D.C. Here is the codebook to help explain the variable, followed by the data example (which I now realise I should have given from the get-go):

        Code:
        . codebook state
        
        ----------------------------------------------------------------------------------------------------------------------
        state                                                                                                            state
        ----------------------------------------------------------------------------------------------------------------------
        
                          type:  numeric (float)
                         label:  state
        
                         range:  [1,51]                       units:  1
                 unique values:  51                       missing .:  26/919
        
                      examples:  11    GA
                                 21    MD
                                 35    NY
                                 44    TX
        Code:
        * Example generated by -dataex-. To install: ssc install dataex
        clear
        input float state
        1
        1
        1
        1
        1
        2
        2
        2
        2
        2
        2
        2
        3
        3
        3
        3
        3
        3
        4
        4
        4
        4
        4
        4
        4
        4
        4
        4
        4
        4
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        5
        end
        label values state state
        label def state 1 "AK", modify
        label def state 2 "AL", modify
        label def state 3 "AR", modify
        label def state 4 "AZ", modify
        label def state 5 "CA", modify
        When I follow your advice the way I understood it, and use the list command, I get a number associated with each observation, not with each unique value of the variable 'state'. See the code and stata output below (just showing the first 20 obs):

        Code:
         Listed 100 out of 919 observations
        Use the count() option to list more
        
        . list state
        
             +-------+
             | state |
             |-------|
          1. |    AK |
          2. |    AK |
          3. |    AK |
          4. |    AK |
          5. |    AK |
             |-------|
          6. |    AL |
          7. |    AL |
          8. |    AL |
          9. |    AL |
         10. |    AL |
             |-------|
         11. |    AL |
         12. |    AL |
         13. |    AR |
         14. |    AR |
         15. |    AR |
             |-------|
         16. |    AR |
         17. |    AR |
         18. |    AR |
         19. |    AZ |
         20. |    AZ |
             |-------|
        So I have not really understood how the list command could help in my case. I hope maybe the data examples make my question clearer?

        Comment


        • #5
          Mike Lacy, thank you for this advice. I didn't know the numlabel command yet, and I'm sure it will be useful in conjunction with the option to separate text into two columns in excel. However, I am still hoping for a full-automatic way to export a frequency table like the one shown in my screenshot above. I simply have to share such tables a lot in my work, and code that could output a finished table to excel would save me a lot of time. But as work-arounds go, this one is certainly not very time-intensive, so thank you!

          Comment


          • #6
            Here's another idea. The following is probably not very efficient, but it's easy.


            Code:
            preserve
            decode state, gen(statelabel) // variable with label values
            contract state statelabel, freq(Freq) percent(Percent) cpercent(Cum)
            export excel using MyFile.xls, ///
               cell("A1") firstrow(variables) nolabel
            restore

            Comment

            Working...
            X