export table results with weighted counts

[Elise Gordon]

Hi everyone,

I have been trying to either save my tabulated results as a dataset or export the results to xlsx/csv and have run into issues because my command involves tabulating counts weighted by survey weights (pweights) and a large number of values.

I would like to create a dataset from my crosstab of weighted counts by country of birth (n=100) and survey country (n=68). This is the command I use to produce the table I want to be able to export/save.

svy: tab country_birth country_survey if outcome==1, count format(%11.0f)

I have tried to use tabxl, tabout, and putexcel but keep getting errors because of too many values (tabout) or pweights not being allowed (tab2xl).

Any suggestions as to how I can do this simply would be much appreciated. My present workaround is to collapse by country of birth and survey country and then reshape to wide on the country of birth which then requires long, tedious code to rename variables.

Thanks in advance!

Elise

[Andrew Musau]

ssc install estout, replace

webuse nhanes2b, clear
svyset psuid [pweight=finalwgt], strata(stratid)
estpost svy: tab race diabetes, count format(%12.0f)
esttab . using myfile.csv, replace main(b) mlab(none) ///
noobs not nonotes nonumbers unstack ///
title("Counts by race") ///
nostar label noabbrev

Rea.:

. estpost svy: tab race diabetes, count format(%12.0f)
(running tabulate on estimation sample)

Number of strata = 31                            Number of obs   =      10,349
Number of PSUs   = 62                            Population size = 117,131,111
                                                 Design df       =          31

-------------------------------------------
          |         Diabetes status        
     Race |  Not diab   Diabetic      Total
----------+--------------------------------
    White |  99682793    3290354  102973147
    Black |  10528681     660555   11189236
    Other |   2908356      60372    2968728
          | 
    Total | 113119830    4011281  117131111
-------------------------------------------
Key: Weighted count

  Pearson:
    Uncorrected   chi2(2)         =   21.3483
    Design-based  F(1.52, 47.26)  =   15.0056     P = 0.0000

. esttab ., replace main(b) mlab(none) ///
> noobs not nonotes nonumbers unstack ///
> title("Counts by race") ///
> nostar label noabbrev

Counts by race
-----------------------------------------------------------
                     Not diabetic     Diabetic        Total
-----------------------------------------------------------
White                    99682793      3290354    102973147
Black                    10528681       660555     11189236
Other                     2908356        60372      2968728
Total                   113119830      4011281    117131111
-----------------------------------------------------------

From the stored results in e(), you can build the same table with collect. See

help collect

[Elise Gordon]

Hi Andrew,

Thanks for your reply.

I did try estpost also but I get the error "too many values" in response to this part of my code:

estpost svy: tab country_birth country_survey if outcome==1, count format(%11.0f)

Let me know if I'm missing something that could get around this.

Thanks!

[Andrew Musau]

Let me know if I'm missing something that could get around this.

Ah, this should be a limit of tabulate. You cannot do anything about it. If your workaround in #1 works, I would work on automating it. Can you provide an example based on #2 for suggestions? I don't work with complex survey data or have to create large tabulations, so others may have better solutions.

[Maarten Buis]

If you have Stata 17 or 18 you could try this (assuming that the weight variable is called weight)

table (country_birth) (country_survey) if outcome==1 [pweight=weight], nformat(%11.0f)
collect export newfile.xlsx, replace

If you have an older version of Stata, then you can create a dataset containing the counts. So

gen one = 1 if !missing(country_birth, country_survey) & outcome == 1
collapse (count) one [pweight=weight], by(country_birth country_survey)
reshape wide weight, i(country_birth) j(country_survey)
list

After that you can export the dataset to excel

[Elise Gordon]

Thanks Maarten, table and collect worked perfectly!