Choose column to Excel

[Anna Stubbendorff]

Hi!

I want to put my margins predictions into Excel. How can I choose to export only the first column (5 values) from my output table and distribute it on one row in Excel? And is it possible to add lower CI and upper CI in paranthesis in the same cell?

And is it possible to get the values in percentage with % as well?

*Probabilities.
logit HGB_belowREF i.CO2_quintiles_sex if (SEX==0)
margins i.CO2_quintiles_sex
matrix b = e(b)'
putexcel A1:E1= matrix(b)

[Jeff Pitblado (StataCorp)]

If you have Stata 17 or newer, you can use the new collect suit
of commands with putexcel to meet your needs.

Here is what I came up with, using some publicly available data.

clear all

webuse nhanes2l

* fit model
logit highlead i.agegrp

* collect margins results
collect _r_b _r_ci : margins agegrp, expression(100*predict(pr))

* proper label for the collected margins
collect label levels result _r_b "Margin", modify

* some style choices compatible to -margins- output
collect style row stack, nobinder
collect style cell result[_r_b _r_ci], nformat(%4.1f)
collect style cell result[_r_ci], sformat("(%s)")
collect layout (colname) (result)

* put margin and CI in a single cell
collect composite define mystats = _r_b _r_ci, trim
* center instead of repeating column headers
collect style column, dups(center)
* arrange margin stats in a single row
collect layout (result[mystats]) (colname)

* open spreadsheet
putexcel set sheet.xlsx, open replace

* export collection to spreadsheet
putexcel A1 = collect

* if you do not want row or column headings, try
collect style header, title(hide) level(hide)
* remove the borders too
collect style cell border_block, border(all, pattern(none))
collect preview
* export to spreadsheet
putexcel A5 = collect

* save/close spreadsheet
putexcel save

The following is a screenshot of the resulting spreadsheet in
LibreOffice on my Mac.

Row 3 contains the values starting in cell B3, and is adorned with row and
column headers and some borders.

Row 5 contains the values without any extra adornments.

[Anna Stubbendorff]

Thank you so much! It looks great!

I do get a problem exporting to Excel, though. Do you know why?

This is a short version of the code:
quietly logit HGB_belowREF AGE i.CO2_quintiles_sex if(SEX==0)
margins i.CO2_quintiles_sex
collect _r_b _r_ci : margins CO2_quintiles_sex, expression(100*predict(pr))
collect composite define mystats6 = _r_b _r_ci, trim
collect label levels result _r_b "Margin", modify
putexcel set margins.xlsx, open replace
putexcel A1 = collect
putexcel save

[Anna Stubbendorff]

One more question, if I may :-).

If I want to add the beta and p-value to the spread sheet, how would you do that? And is it possible to add all the code to a loop? How do I control that for each variable I start on the row below?


Loop example:
foreach y in Serum_vitD_belowREF Serum_sele_belowREF Serum_zn_belowREF Serum_fol_belowREF HGB_belowREF {
quietly logit `y' AGE DATUM i.SEASON i.CO2_quintiles_sex if(SEX==0)
margins i.CO2_quintiles_sex
logit `y' CO2_quintiles_sex AGE DATUM i.SEASON if(SEX==0)
}

Simplified desired output

Thank you in advance!

[Jeff Pitblado (StataCorp)]

The error from putexcel in #3 happens when you fail to call
putexcel save in your previous do-file run.
Type putexcel clear to start a new putexcel session.

My example dataset in #2 does not have a variable like your
CO2_quijntiles_sex that can be treated as continuous or
categorical like you show in #4. So in the following I simulate a
similar looking dataset to the one you show in #4, that way I can show
you how I modified your code and can verify that it works as I intend.

* simulate some data
set obs 30000
set seed 18
foreach y in Serum_vitD_belowREF Serum_sele_belowREF Serum_zn_belowREF Serum_fol_belowREF HGB_belowREF {
    gen byte `y' = runiform() > .95
}
gen byte SEX = runiformint(0,1)
gen AGE = runiformint(25,65)
gen DATUM = rnormal()
gen SEASON = runiformint(1,4)
gen CO2_quintiles_sex = runiformint(1,5)

* start with a new collection
collect clear
foreach y in Serum_vitD_belowREF Serum_sele_belowREF Serum_zn_belowREF Serum_fol_belowREF HGB_belowREF {
    quietly logit `y' AGE DATUM i.SEASON i.CO2_quintiles_sex if(SEX==0)
    * collect the marginal predicted probabilities as percents
    collect _r_b, tags(var[`y']) : ///
        margins i.CO2_quintiles_sex, expression(100*predict(pr))
    logit `y' CO2_quintiles_sex AGE DATUM i.SEASON if(SEX==0)
    * collect beta and p-trend
    collect get ///
        beta=(_r_b[CO2_quintiles_sex]) ///
        ptrend=(_r_p[CO2_quintiles_sex]) ///
        , tags(var[`y'])
}
collect style column, dups(center)
* formats
collect style cell result[_r_b], nformat(%4.1f) sformat("%s%%")
collect style cell result[beta], nformat(%9.2f)
collect style cell result[ptrend], nformat(%5.3f)
* labels
collect label levels result beta "β" ptrend "p-trend", modify
* select results to show/hide in the header
collect style header result[_r_b], level(hide)
* layout
collect layout (var) (colname#result[_r_b] result[beta ptrend])

Here is the resulting table.

------------------------------------------------------------
                    |     CO2_quintiles_sex        β p-trend
                    |    1    2    3    4    5
--------------------+---------------------------------------
Serum_vitD_belowREF | 5.0% 4.2% 5.4% 5.1% 5.0%  0.02   0.477
Serum_sele_belowREF | 5.4% 4.9% 6.2% 5.1% 5.8%  0.02   0.483
Serum_zn_belowREF   | 5.0% 4.8% 4.9% 5.2% 4.6% -0.01   0.670
Serum_fol_belowREF  | 4.7% 5.1% 5.0% 4.9% 4.7% -0.01   0.850
HGB_belowREF        | 5.0% 4.6% 4.9% 4.7% 4.7% -0.01   0.632
------------------------------------------------------------

If your dataset has labels on the y variables and CO2_quintiles_sex,
then those labels would show up in the headers instead of the variable names.

[Anna Stubbendorff]

Great, thank you!

Just one more question: Should it be a logistic regression or linear regression for the analyses where I collect my beta and p-trend? The results differes slightly if i use regress instead.

*start with a new collection
collect clear
foreach y in Serum_vitD_belowREF Serum_sele_belowREF Serum_zn_belowREF Serum_fol_belowREF HGB_belowREF {
quietly logit `y' AGE DATUM i.SEASON i.CO2_quintiles_sex if(SEX==0)
* collect the marginal predicted probabilities as percents
collect _r_b, tags(var[`y']) : ///
margins i.CO2_quintiles_sex, expression(100*predict(pr))
logit `y' CO2_quintiles_sex AGE DATUM i.SEASON if(SEX==0)
* collect beta and p-trend
collect get ///
beta=(_r_b[CO2_quintiles_sex]) ///
ptrend=(_r_p[CO2_quintiles_sex]) ///
, tags(var[`y'])

[Anna Stubbendorff]

If I want to put the results from my loop into a table into pre-choosen cells in Excel, is it possible to do it?

[Jeff Pitblado (StataCorp)]

Regarding #6, I will defer to your scientific reasoning or some other expert in this area.

Regarding #7, refer to my code example in #2 where I use putexcel A5 = collect to export the table to the spreadsheet starting with its upper left corner in cell A5.