Put in excel: row proportions with svy

[Krystel Chem]

Hello everyone!

I have a simple question ; I want to "put in excel" weighted row proportions.

I was writing the following codes:

            
svy: tab Outcome Covariate, row 
local Per=(e(Prop)[2,1]*100)
local Per_s=string(`Per', "%9.1f")
putexcel F4 =  `"`Per_s'"'

Outcome=BMI_catOb
Covariate=gender
*****************
. svy: tab BMI_catOb gender, row
(running tabulate on estimation sample)

Number of strata =  2                             Number of obs   =      2,465
Number of PSUs   = 50                             Population size = 158,768.93
                                                  Design df       =         48

----------------------------------
RECODE of |
bmiz_cat  |
(BMI 5    |      Child Gender     
cat)      |   Male  Female   Total
----------+-----------------------
 Non Obes |  .4762   .5238       1
  Obesity |  .5977   .4023       1
          | 
    Total |  .4972   .5028       1
----------------------------------
Key: Row proportion

  Pearson:
    Uncorrected   chi2(1)         =   20.8483
    Design-based  F(1, 48)        =   13.4654     P = 0.0006

. svy: tab BMI_catOb gender
(running tabulate on estimation sample)

Number of strata =  2                             Number of obs   =      2,465
Number of PSUs   = 50                             Population size = 158,768.93
                                                  Design df       =         48

----------------------------------
RECODE of |
bmiz_cat  |
(BMI 5    |      Child Gender     
cat)      |   Male  Female   Total
----------+-----------------------
 Non Obes |  .3938   .4332   .8269
  Obesity |  .1035   .0696   .1731
          | 
    Total |  .4972   .5028       1
----------------------------------
Key: Cell proportion

  Pearson:
    Uncorrected   chi2(1)         =   20.8483
    Design-based  F(1, 48)        =   13.4654     P = 0.0006

The problem is that the e(Prop) is giving me the weighted cell proportion and I am not being able to get the row proportion (e.g. 59% and not 10%).

Thank you!

[Andrew Musau]

For your future posts, present a reproducible example as recommended in FAQ Advice #12. Results are stored in either e() or r(), depending on the command. See either

return list

or

ereturn list

webuse nhanes2b, clear
svyset psuid [pweight=finalwgt], strata(stratid)
svy: tabulate race region
ereturn list
mat list e(b)
display e(b)[1, "p11"]
display e(b)[1, "p32"]

Res.:

. svy: tabulate race region
(running tabulate on estimation sample)

Number of strata = 31                            Number of obs   =      10,351
Number of PSUs   = 62                            Population size = 117,157,513
                                                 Design df       =          31

---------------------------------------------
1=white,  |
2=black,  |       1=NE, 2=MW, 3=S, 4=W      
3=other   |    NE     MW      S      W  Total
----------+----------------------------------
    White | .1961  .2224  .2166   .244  .8792
    Black | .0095  .0244  .0468  .0148  .0955
    Other | .0013  .0021  .0018  .0201  .0253
          |
    Total | .2069  .2489  .2653  .2789      1
---------------------------------------------
  Key:  cell proportion

  Pearson:
    Uncorrected   chi2(6)         =  667.4836
    Design-based  F(3.38, 104.91) =    9.1347     P = 0.0000

 
. mat list e(b)

e(b)[1,12]
          p11        p12        p13        p14        p21        p22        p23        p24        p31        p32        p33
y1  .19606509  .22242168  .21664883  .24401885   .0094961  .02438053  .04683628  .01479302  .00132178  .00212589  .00180632

          p34
y1  .02008564

.
. display e(b)[1, "p11"]
.19606509

.
. display e(b)[1, "p32"]
.00212589

[Krystel Chem]

Hi Andrew! thank you for your detailed answer. Much appreciated!
However, these are not the % that I am trying to retrieve.
I am going to use the example you gave me to illustrate my question.
In the above example the e(b) is showing the cell proportions. I am trying to have the row proportions (weighted).

Below is what you have shown:

. svy: tabulate race region
(running tabulate on estimation sample)

Number of strata = 31                            Number of obs   =      10,351
Number of PSUs   = 62                            Population size = 117,157,513
                                                 Design df       =          31

---------------------------------------------
          |              Region              
     Race |    NE     MW      S      W  Total
----------+----------------------------------
    White | .1961  .2224  .2166   .244  .8792
    Black | .0095  .0244  .0468  .0148  .0955
    Other | .0013  .0021  .0018  .0201  .0253
          | 
    Total | .2069  .2489  .2653  .2789      1
---------------------------------------------
Key: Cell proportion

  Pearson:
    Uncorrected   chi2(6)         =  667.4836
    Design-based  F(3.38, 104.91) =    9.1347     P = 0.0000

. mat list e(b)

e(b)[1,12]
          p11        p12        p13        p14        p21        p22        p23        p24        p31        p32
y1  .19606509  .22242168  .21664883  .24401885   .0094961  .02438053  .04683628  .01479302  .00132178  .00212589

          p33        p34
y1  .00180632  .02008564

Below is the % that I want. As you can see when I write mat list e(b) I only get the cell proportions and not the row proportions. the numbers are different.

. svy: tabulate race region, row
(running tabulate on estimation sample)

Number of strata = 31                            Number of obs   =      10,351
Number of PSUs   = 62                            Population size = 117,157,513
                                                 Design df       =          31

---------------------------------------------
          |              Region              
     Race |    NE     MW      S      W  Total
----------+----------------------------------
    White |  .223   .253  .2464  .2776      1
    Black | .0994  .2553  .4904  .1549      1
    Other | .0522  .0839  .0713  .7927      1
          | 
    Total | .2069  .2489  .2653  .2789      1
---------------------------------------------
Key: Row proportion

  Pearson:
    Uncorrected   chi2(6)         =  667.4836
    Design-based  F(3.38, 104.91) =    9.1347     P = 0.0000

. mat list e(b)

e(b)[1,12]
          p11        p12        p13        p14        p21        p22        p23        p24        p31        p32
y1  .19606509  .22242168  .21664883  .24401885   .0094961  .02438053  .04683628  .01479302  .00132178  .00212589

          p33        p34
y1  .00180632  .02008564

Many thanks Andrew!

[Andrew Musau]

It seems that these are not stored. Here is a program that will create such a matrix. Include the program in your do-file and run it after each estimation to get the statistics.

cap program drop rowmat
program rowmat, eclass
forval row = 1/`=e(r)'{
    mat t`row'=[0]
    forval col= 1/`=e(c)'{    
          mat t`row'= t`row'+ e(b)[1, "p`row'`col'"]
    }
}
forval row = 1/`=e(r)'{
    mat hold`row'=e(b)[1, "p`row'1".."p`row'`=e(c)'"]/`=t`row'[1,1]'
    matrix drop t`row'
}
mat brow= hold1
forval i = 2/`=e(r)'{
    mat brow= brow, hold`i'
}
ereturn matrix brow= brow
end

*ILLUSTRATED EXAMPLE
webuse nhanes2b, clear
svyset psuid [pweight=finalwgt], strata(stratid)
svy: tabulate race region, row
rowmat
mat l e(brow)

Res.:

. svy: tabulate race region, row
(running tabulate on estimation sample)

Number of strata = 31                            Number of obs   =      10,351
Number of PSUs   = 62                            Population size = 117,157,513
                                                 Design df       =          31

---------------------------------------------
1=white,  |
2=black,  |       1=NE, 2=MW, 3=S, 4=W       
3=other   |    NE     MW      S      W  Total
----------+----------------------------------
    White |  .223   .253  .2464  .2776      1
    Black | .0994  .2553  .4904  .1549      1
    Other | .0522  .0839  .0713  .7927      1
          | 
    Total | .2069  .2489  .2653  .2789      1
---------------------------------------------
  Key:  row proportion

  Pearson:
    Uncorrected   chi2(6)         =  667.4836
    Design-based  F(3.38, 104.91) =    9.1347     P = 0.0000

. 
. rowmat

. 
. mat l e(brow)

e(brow)[1,12]
          p11        p12        p13        p14        p21        p22        p23        p24        p31        p32        p33
y1  .22301552    .252995  .24642863  .27756085   .0994294  .25527766  .49040185  .15489109  .05216241  .08389586   .0712844

          p34
y1  .79265733

[Jeff Pitblado (StataCorp)]

You can use option se with option row to force svy: tabulate to post the row proportions to e(b).

Here is the above example, using option se.

. webuse nhanes2b

. svyset

Sampling weights: finalwgt
             VCE: linearized
     Single unit: missing
        Strata 1: stratid
 Sampling unit 1: psuid
           FPC 1: <zero>

. svy: tabulate race region, row se
(running tabulate on estimation sample)

Number of strata = 31                            Number of obs   =      10,351
Number of PSUs   = 62                            Population size = 117,157,513
                                                 Design df       =          31

-------------------------------------------------------
          |                   Region
     Race |      NE       MW        S        W    Total
----------+--------------------------------------------
    White |    .223     .253    .2464    .2776        1
          | (.0078)  (.0089)  (.0167)  (.0131)
          |
    Black |   .0994    .2553    .4904    .1549        1
          | (.0243)  (.0468)  (.0653)  (.0627)
          |
    Other |   .0522    .0839    .0713    .7927        1
          | (.0287)  (.0431)  (.0343)  (.0921)
          |
    Total |   .2069    .2489    .2653    .2789        1
          | (.0056)  (.0062)  (.0104)  (.0114)
-------------------------------------------------------
Key: Row proportion
     (Linearized standard error of row proportion)

  Pearson:
    Uncorrected   chi2(6)         =  667.4836
    Design-based  F(3.38, 104.91) =    9.1347     P = 0.0000

. matrix list e(b)

e(b)[1,12]
          p11        p12        p13        p14        p21        p22        p23        p24        p31        p32        p33
y1  .22301552    .252995  .24642863  .27756085   .0994294  .25527766  .49040185  .15489109  .05216241  .08389586   .0712844

          p34
y1  .79265733