Put-excel for prevalence ratio

[Hassan Mir]

Hi,

I am using STATA 13 with windows 7.

I want to report prevalence ration using these lines:

logit bmi3 i.inactive age18 female white diet bmi2, or
margins inactive, post
margins, coeflegend
nlcom (ratio1: _b[1.inactive]/_b[0bn.inactive]), post
test ratio1 = 1

Then I want to use the putexcel function.

But when I used this code it did not work. I would appreciate your input.

logit bmi3 i.inactive age18 female white diet bmi2, or
margins inactive, post
margins, coeflegend
nlcom (ratio1: _b[1.inactive]/_b[0bn.inactive]), post
matrix M = r(table)
putexcel A1= (M[1,1]) A2=(M[1,2]) A3=(M[1,3]) B1=(M[2,1]) B2=(M[2,2]) B3=(M[2,3]) using testrevision.xlsx, modify
test ratio1 = 1

[William Lisowski]

Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

Section 12.1 is particularly pertinent

12.1 What to say about your commands and your problem

Say exactly what you typed and exactly what Stata typed (or did) in response. N.B. exactly!
...
Never say just that something "doesn't work" or "didn't work", but explain precisely in what sense you didn't get what you wanted.

It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using CODE delimiters, as described in section 12 of the FAQ.

[Hassan Mir]

Thanks for your response.

I ran the quoted codes to have excel file of my output. My codes worked perfectly and I was able to get the numbers I wanted.

ogit bmi3 i.inactive age18 female white diet bmi2, or
margins inactive, post
margins, coeflegend
nlcom (ratio1: _b[1.inactive]/_b[0bn.inactive]), post
test ratio1 = 1

Unfortunately, putexcel did not work. I mean it will only save an empty excel file.

[Isaac Maddow-Zimet]

Try this instead (replacing your putexcel line of code):

putexcel set testrevision.xlsx, modify 
putexcel A1= matrix(M)

If you only want to export some rows and columns of the matrix, assign them to a new matrix before exporting:

matrix N = M[1..2, 1..3]
putexcel set testrevision.xlsx, modify 
putexcel A1= matrix(N)

Hopefully this is helpful.

[Hassan Mir]

Thanks for your response.

It did not work.

here you can see my output but I got an empty excel.







. svy: logit bmi3 i._inactive i.agegroup, or
(running logit on estimation sample)

Survey: Logistic regression

Number of strata = 1654 Number of obs = 461054
Number of PSUs = 461054 Population size = 237930601
Design df = 459400
F( 20, 459381) = 109.09
Prob > F = 0.0000

------------------------------------------------------------------------------
| Linearized
bmi3 | Odds Ratio Std. Err. t P>|t| [95% Conf. Interval]
-------------+----------------------------------------------------------------
bmi3 |
2 | .561438 .0343213 -9.44 0.000 .498043 .6329025
3 | 1.268738 .1386369 2.18 0.029 1.024141 1.571753
4 | .4501635 .0476953 -7.53 0.000 .36575 .5540593
5 | .9737232 .1902326 -0.14 0.892 .6639556 1.428012
6 | .8246319 .1456552 -1.09 0.275 .5833262 1.165759
7 | 1.564371 .1950244 3.59 0.000 1.225246 1.997359
8 | .4295201 .0253673 -14.31 0.000 .3825707 .4822312
9 | .7916435 .1009837 -1.83 0.067 .6165216 1.016509
|
agegroup |
2 | .7878276 .044007 -4.27 0.000 .7061291 .8789786
3 | .5862484 .032819 -9.54 0.000 .5253274 .6542342
4 | .5326143 .0313368 -10.71 0.000 .4746042 .597715
5 | .4670217 .0318212 -11.17 0.000 .4086383 .5337465
6 | .4315585 .0309968 -11.70 0.000 .3748881 .4967955
7 | .3634337 .0250085 -14.71 0.000 .3175795 .4159088
8 | .3262567 .0187533 -19.49 0.000 .2914957 .3651631
9 | .2389207 .0142056 -24.08 0.000 .2126392 .2684505
10 | .1469099 .0100365 -28.07 0.000 .1284989 .1679589
11 | .1090628 .0115424 -20.94 0.000 .0886321 .134203
12 | .0594873 .0089007 -18.86 0.000 .0443674 .07976
13 | .0185227 .0048733 -15.16 0.000 .0110599 .0310209
|
_cons | .1303265 .0045731 -58.07 0.000 .1216646 .1396051
------------------------------------------------------------------------------

. margins inactive, post

Predictive margins Number of obs = 461054
ModelVCE : Linearized

Expression : Pr(inactive), predict()

------------------------------------------------------------------------------
| Delta-method
| Margin Std. Err. z P>|z| [95% Conf. Interval]
-------------+----------------------------------------------------------------
inactive|
1 | .055313 .0009056 61.08 0.000 .053538 .0570879
2 | .0321153 .00179 17.94 0.000 .0286071 .0356236
3 | .068798 .0067331 10.22 0.000 .0556013 .0819946
4 | .025977 .0026157 9.93 0.000 .0208504 .0311036
5 | .0539657 .0097269 5.55 0.000 .0349013 .0730301
6 | .0462211 .0076131 6.07 0.000 .0312997 .0611424
7 | .0830447 .0090988 9.13 0.000 .0652115 .100878
8 | .0248263 .0013475 18.42 0.000 .0221852 .0274675
9 | .0444839 .0052849 8.42 0.000 .0341258 .0548421
------------------------------------------------------------------------------

. margins, coeflegend

Predictive margins Number of obs = 461054
ModelVCE : Linearized

Expression : Pr(bmi3), predict()

------------------------------------------------------------------------------
| Margin Legend
-------------+----------------------------------------------------------------
inactive|
1 | .055313 _b[1bn._race]
2 | .0321153 _b[2._race]
3 | .068798 _b[3._race]
4 | .025977 _b[4._race]
5 | .0539657 _b[5._race]
6 | .0462211 _b[6._race]
7 | .0830447 _b[7._race]
8 | .0248263 _b[8._race]
9 | .0444839 _b[9._race]
------------------------------------------------------------------------------

. nlcom (ratio1: _b[2.inactive]/_b[1bn.inactive]), post

ratio1: _b[2.inactive]/_b[1bn.inactive]

------------------------------------------------------------------------------
| Coef. Std. Err. z P>|z| [95% Conf. Interval]
-------------+----------------------------------------------------------------
ratio1 | .5806111 .0338129 17.17 0.000 .514339 .6468832
------------------------------------------------------------------------------

. matrix M = r(table)

. putexcel A1= matrix(M)
file testrevision.xlsx saved

. test ratio1 = 1

( 1) ratio1 = 1

chi2( 1) = 153.84
Prob > chi2 = 0.0000

[Isaac Maddow-Zimet]

nlcom doesn't store its results in r(table), as far as I know, so I think you're just assigning an empty matrix to M. e(b) has the coefficient from nlcom, if that's what you are looking to export (it's not clear to me whether you are trying to export the results of your test command as well; if you are, you need to refer to those results separately, or add them to the matrix before you export.).

[William Lisowski]

The output of help nlcom says nothing about it returning r(table). Thus it seems likely that the matrix M was empty, which is why nothing got written. Running

matrix M = r(table)
matrix list M

will allow you to see whether matrix M contains what you expect.

If you wanted r(table) from some earlier command, you need to store it with your matrix command immediately after the command that created it. Perhaps it was the svy: logit command?