Exporting weighted cross tabulation from stata to excel

[Rosa Blau]

Hello,

I'm looking to export a weighted cross tabulation from stata to excel. The variables I am tabulating are 'naics2017' and 'skills' (both labeled integers).

I just also posted about this in another thread here, but since the only other participant in that thread is not a frequent poster, I have decided to start this separate thread on this question.

This is my stata code:

svyset cluster [pweight = perwt], strata(strata)
tab naics2017 skill [iweight = perwt], row

And this is my stata output, which I would like to export to excel:

              
            naics2017 |       n/a  low-gener  high-gene   specific |     Total
----------------------+--------------------------------------------+----------
           Unemployed |   1863042          0          0          0 |   1863042 
                      |    100.00       0.00       0.00       0.00 |    100.00 
----------------------+--------------------------------------------+----------
  Outside Labor Force | 132248332          0          0          0 | 132248332 
                      |    100.00       0.00       0.00       0.00 |    100.00 
----------------------+--------------------------------------------+----------
Agriculture, Forestry |    199944     196790     922683    1317452 |   2636869 
                      |      7.58       7.46      34.99      49.96 |    100.00 
----------------------+--------------------------------------------+----------
Mining, Quarrying, an |        94      68227     452293     409313 |    929927 
                      |      0.01       7.34      48.64      44.02 |    100.00 
----------------------+--------------------------------------------+----------
           Utilities  |      1385     258077     840629     365116 |   1465207 
                      |      0.09      17.61      57.37      24.92 |    100.00 
----------------------+--------------------------------------------+----------
        Construction  |      4628    2807564    3410727    5822133 |  12045052 
                      |      0.04      23.31      28.32      48.34 |    100.00 
----------------------+--------------------------------------------+----------
        Manufacturing |      7194    2454982    7705827    8358803 |  18526806 
                      |      0.04      13.25      41.59      45.12 |    100.00 
----------------------+--------------------------------------------+----------
     Wholesale Trade  |      1633    1046252    2797857     885295 |   4731037 
                      |      0.03      22.11      59.14      18.71 |    100.00 
----------------------+--------------------------------------------+----------
        Retail Trade  |      2569   16907834    3451282    1928593 |  22290278 
                      |      0.01      75.85      15.48       8.65 |    100.00 
----------------------+--------------------------------------------+----------
Transportation and Wa |      2096    2790155    1521786    3782979 |   8097016 
                      |      0.03      34.46      18.79      46.72 |    100.00 
----------------------+--------------------------------------------+----------
         Information  |       300     746352    2636463     406738 |   3789853 
                      |      0.01      19.69      69.57      10.73 |    100.00 
----------------------+--------------------------------------------+----------
Finance and Insurance |         0    1901401    6101235      41115 |   8043751 
                      |      0.00      23.64      75.85       0.51 |    100.00 
----------------------+--------------------------------------------+----------
Real Estate and Renta |      2299     822441    2573051     272503 |   3670294 
                      |      0.06      22.41      70.10       7.42 |    100.00 
----------------------+--------------------------------------------+----------
Professional, Scienti |      2045    1040144   11183257     296107 |  12521553 
                      |      0.02       8.31      89.31       2.36 |    100.00 
----------------------+--------------------------------------------+----------
Management of Compani |       123      27786     161661       5979 |    195549 
                      |      0.06      14.21      82.67       3.06 |    100.00 
----------------------+--------------------------------------------+----------
Administrative and Su |    170487    4751960    2719510     926562 |   8568519 
                      |      1.99      55.46      31.74      10.81 |    100.00 
----------------------+--------------------------------------------+----------
Educational Services  |      3297    3651622   13053558     542366 |  17250843 
                      |      0.02      21.17      75.67       3.14 |    100.00 
----------------------+--------------------------------------------+----------
Health Care and Socia |      2147    8554652   16190130     408883 |  25155812 
                      |      0.01      34.01      64.36       1.63 |    100.00 
----------------------+--------------------------------------------+----------
Arts, Entertainment,  |     18950    1933996    2313321     406636 |   4672903 
                      |      0.41      41.39      49.51       8.70 |    100.00 
----------------------+--------------------------------------------+----------
Accommodation and Foo |      1020   11682777    3161060     552139 |  15396996 
                      |      0.01      75.88      20.53       3.59 |    100.00 
----------------------+--------------------------------------------+----------
Other Services (excep |      4977    4454161    3106563    1803892 |   9369593 
                      |      0.05      47.54      33.16      19.25 |    100.00 
----------------------+--------------------------------------------+----------
Public Administration |    610192    1991894    6527783     528414 |   9658283 
                      |      6.32      20.62      67.59       5.47 |    100.00 
----------------------+--------------------------------------------+----------
                Total | 135146754   68089067   90830676   29061018 | 323127515 
                      |     41.82      21.07      28.11       8.99 |    100.00

I would appreciate your advice on this roadblock.

Best,
Rosa

[Andrew Musau]

ssc install estout
svyset cluster [pweight = perwt], strata(strata)
estpost tab naics2017 skill [iweight = perwt]
mat out=(e(b)\e(rowpct))'
esttab mat(out) using myfile.csv, nomtitles collabels("")

[Rosa Blau]

Thank you, Andrew.

This command enabled me to export my table to excel. However, the formatting is still quite off, as illustrated by my screenshot below. The entire table is crammed into a single column: Each row arrays a value of 'naics2017', along with its frequency and percentage; and the values of 'skills' are displayed listwise on top of each other.

This is the first time I am using estpost, but I assume the key to the display is in the 'mat out' part (is it an option of estpost?). Unfortunately, I can't seem to find 'mat out' in the stata help manual, so I can't taylor it to make the table display in excel as it does in stata (shown above).

I would appreciate further guidance on how to taylor this command to my specifications.

Best,

Rosa

[Andrew Musau]

This command enabled me to export my table to excel. However, the formatting is still quite off, as illustrated by my screenshot below. The entire table is crammed into a single column: Each row arrays a value of 'naics2017', along with its frequency and percentage; and the values of 'skills' are displayed listwise on top of each other.

I suspect that you changed the output format to .xls. I intentionally specified .csv to deal with this issue. You need to export as a csv file and then open this in Excel. To get the the results in the same row, do not transpose the matrix. So change the line of code to

mat out=(e(b)\e(rowpct))

I will try to work out something close to the display of tabulate later today when I get the time.

[Andrew Musau]

Combining the matrices is unnecessary, there are options within esttab that allow you to precisely obtain the table displayed by tab, row. Still holds that you need to save as a csv file and open this in Excel to obtain separate columns.

svyset cluster [pweight = perwt], strata(strata)
estpost tab naics2017 skill [iweight = perwt]
esttab . using myfile.csv, cell(b rowpct(fmt(2))) unstack ///
noobs collabels("")  nomtitle nonumber replace

[Rosa Blau]

Hi Andrew -- thank you very much for taking the time to look at this again.

As you suspected, I did change the output format from .csv to .xls. But that is because when I opened the csv in excel, it was entirely unformatted, which is what is happening again with the estpost command.

Do you have any idea why this might be the case? I am exporting it as a .csv and opening it in excel, as you said.

Using your exact code, I get the following stata output:

. do "/var/folders/cr/qj4lhc096kx35xpbg_j8m8kh0000gn/T//SD43426.000000"

. svyset cluster [pweight = perwt], strata(strata)

      pweight: perwt
          VCE: linearized
  Single unit: missing
     Strata 1: strata
         SU 1: cluster
        FPC 1: <zero>

. estpost tab naics2017 skill [iweight = perwt]

skill        |                                            
   naics2017 |      e(b)     e(pct)  e(colpct)  e(rowpct) 
-------------+--------------------------------------------
n/a          |                                            
          -1 |   1863042   .5765656   1.378533        100 
           0 |  1.32e+08    40.9276   97.85535        100 
          11 |    199944   .0618777   .1479458   7.582629 
          21 |        94   .0000291   .0000696   .0101083 
          22 |      1385   .0004286   .0010248   .0945259 
          23 |      4628   .0014323   .0034244   .0384224 
          31 |      7194   .0022264   .0053231   .0388302 
          42 |      1633   .0005054   .0012083   .0345167 
          44 |      2569    .000795   .0019009   .0115252 
          48 |      2096   .0006487   .0015509   .0258861 
          51 |       300   .0000928    .000222   .0079159 
          52 |         0          0          0          0 
          53 |      2299   .0007115   .0017011    .062638 
          54 |      2045   .0006329   .0015132   .0163318 
          55 |       123   .0000381    .000091   .0628998 
          56 |    170487   .0527615   .1261495    1.98969 
          61 |      3297   .0010203   .0024396   .0191121 
          62 |      2147   .0006644   .0015886   .0085348 
          71 |     18950   .0058646   .0140218   .4055295 
          72 |      1020   .0003157   .0007547   .0066247 
          81 |      4977   .0015403   .0036827   .0531186 
          92 |    610192   .1888394   .4515033    6.31781 
       Total |  1.35e+08   41.82459        100   41.82459 
-------------+--------------------------------------------
low-general  |                                            
          -1 |         0          0          0          0 
           0 |         0          0          0          0 
          11 |    196790   .0609017   .2890185   7.463018 
          21 |     68227   .0211146   .1002026   7.336812 
          22 |    258077   .0798685   .3790285   17.61369 
          23 |   2807564   .8688718    4.12337   23.30886 
          31 |   2454982   .7597564   3.605545   13.25097 
          42 |   1046252   .3237892   1.536593   22.11464 
          44 |  1.69e+07   5.232558   24.83194   75.85295 
          48 |   2790155   .8634842   4.097802   34.45905 
          51 |    746352   .2309775   1.096141   19.69343 
          52 |   1901401   .5884367    2.79252   23.63824 
          53 |    822441   .2545252    1.20789   22.40804 
          54 |   1040144   .3218989   1.527623   8.306829 
          55 |     27786   .0085991   .0408083   14.20923 
          56 |   4751960   1.470614   6.979035   55.45836 
          61 |   3651622   1.130087   5.363008   21.16779 
          62 |   8554652   2.647454   12.56391   34.00666 
          71 |   1933996   .5985241   2.840391   41.38746 
          72 |  1.17e+07   3.615531   17.15808   75.87699 
          81 |   4454161   1.378453   6.541668   47.53847 
          92 |   1991894   .6164421   2.925424   20.62369 
       Total |  6.81e+07   21.07189        100   21.07189 
-------------+--------------------------------------------
high-general |                                            
          -1 |         0          0          0          0 
           0 |         0          0          0          0 
          11 |    922683   .2855476   1.015828   34.99161 
          21 |    452293   .1399735   .4979518   48.63747 
          22 |    840629    .260154   .9254902   57.37271 
          23 |   3410727   1.055536   3.755039   28.31642 
          31 |   7705827   2.384763   8.483727   41.59285 
          42 |   2797857   .8658678     3.0803   59.13835 
          44 |   3451282   1.068087   3.799688   15.48335 
          48 |   1521786   .4709553    1.67541   18.79441 
          51 |   2636463   .8159203   2.902613   69.56637 
          52 |   6101235   1.888182   6.717152   75.85062 
          53 |   2573051   .7962959     2.8328   70.10477 
          54 |  1.12e+07   3.460942    12.3122   89.31206 
          55 |    161661   .0500301   .1779806   82.67033 
          56 |   2719510   .8416213   2.994044   31.73839 
          61 |  1.31e+07   4.039754   14.37131    75.6691 
          62 |  1.62e+07   5.010446   17.82452    64.3594 
          71 |   2313321   .7159158    2.54685   49.50501 
          72 |   3161060   .9782701   3.480168   20.53037 
          81 |   3106563   .9614047   3.420169   33.15579 
          92 |   6527783   2.020188    7.18676   67.58741 
       Total |  9.08e+07   28.10986        100   28.10986 
-------------+--------------------------------------------
specific     |                                            
          -1 |         0          0          0          0 
           0 |         0          0          0          0 
          11 |   1317452   .4077189   4.533399   49.96274 
          21 |    409313   .1266723   1.408461   44.01561 
          22 |    365116   .1129944   1.256377   24.91907 
          23 |   5822133   1.801807   20.03417    48.3363 
          31 |   8358803   2.586843   28.76294   45.11735 
          42 |    885295    .273977   3.046332   18.71249 
          44 |   1928593    .596852   6.636357   8.652171 
          48 |   3782979   1.170739   13.01737   46.72066 
          51 |    406738   .1258754     1.3996   10.73229 
          52 |     41115   .0127241   .1414782   .5111421 
          53 |    272503    .084333   .9376925   7.424555 
          54 |    296107   .0916378   1.018915   2.364779 
          55 |      5979   .0018504    .020574   3.057546 
          56 |    926562   .2867481   3.188333   10.81356 
          61 |    542366   .1678489   1.866301   3.143997 
          62 |    408883   .1265392   1.406981   1.625402 
          71 |    406636   .1258438   1.399249      8.702 
          72 |    552139   .1708734    1.89993   3.586018 
          81 |   1803892   .5582601   6.207257   19.25262 
          92 |    528414   .1635311   1.818291   5.471097 
       Total |  2.91e+07   8.993669        100   8.993669 
-------------+--------------------------------------------
Total        |                                            
          -1 |   1863042   .5765656   .5765656        100 
           0 |  1.32e+08    40.9276    40.9276        100 
          11 |   2636869    .816046    .816046        100 
          21 |    929927   .2877895   .2877895        100 
          22 |   1465207   .4534454   .4534454        100 
          23 |  1.20e+07   3.727647   3.727647        100 
          31 |  1.85e+07    5.73359    5.73359        100 
          42 |   4731037   1.464139   1.464139        100 
          44 |  2.23e+07   6.898292   6.898292        100 
          48 |   8097016   2.505827   2.505827        100 
          51 |   3789853   1.172866   1.172866        100 
          52 |   8043751   2.489343   2.489343        100 
          53 |   3670294   1.135866   1.135866        100 
          54 |  1.25e+07   3.875112   3.875112        100 
          55 |    195549   .0605176   .0605176        100 
          56 |   8568519   2.651745   2.651745        100 
          61 |  1.73e+07   5.338711   5.338711        100 
          62 |  2.52e+07   7.785104   7.785104        100 
          71 |   4672903   1.446148   1.446148        100 
          72 |  1.54e+07   4.764991   4.764991        100 
          81 |   9369593   2.899658   2.899658        100 
          92 |   9658283      2.989      2.989        100 
       Total |  3.23e+08        100        100        100 

row labels saved in macro e(labels)

. esttab . using myfile.csv, cell(b rowpct(fmt(2))) unstack ///
> noobs collabels("")  nomtitle nonumber replace
(output written to myfile.csv)

. 
end of do-file

Then, opening myfile.csv in Excel, I get this:

Do you know what is happening?

[Andrew Musau]

Maybe the following helps, but it is based on the English language version of Excel.

https://www.copytrans.net/support/ho...file-in-excel/

If you are unable to resolve it, post back and I will offer a workaround.

[Rosa Blau]

Thanks Andrew. I will try a few things and get back to you if nothing works out.

[Rosa Blau]

Hi Andrew -- it worked! Thank you very much, you've saved me a lot of time and nerve.

[Andrew Musau]

Thanks for the closure. Also note, with

row labels saved in macro e(labels)

You need to specify the option highlighted in red to have these labels in the output file.

esttab . using myfile.csv, cell(b rowpct(fmt(2))) unstack ///
noobs collabels("") nomtitle nonumber varlabels(`e(labels)') replace