creating a table automatically extracting coefficients from linear mixed models

[laure rouch]

Dear All,

I would like to automatically create a table that would provide after the following models the table below with the coefficients of the simple effect of grim age for exemple, then linear effect interaction with age and then quadratic effect interaction with age2 etc.

mixed score age##age##c.dnamgrimage_adjage_10 i.sexep || id: age
mixed score age##age##c.ageacc3_horvath_10 i.sexep || id: age
mixed score age##age##c.ageacc3_skinhorvath_10 i.sexep || id: age
mixed score age##age##c.ageacc3_hannum_10 i.sexep || id: age
mixed score age##age##c.ageacc3_levine_10 i.sexep || id: age
mixed score age##age##c.ageacc_iage_10 i.sexep || id: age

Biological Aging Clocks			Sex-adjusted main, linear and quadratic interaction effects	SCORE
Coefficient (95% CI)	p-value
1st generation Epigenetic Aging Clocks		Horvath	bAge accel.	-0.51 (-1.26 ; 0.23)	0.17
bAge accel. * cAge
bAge accel. * cAge²
Skin Horvath	bAge accel.
bAge accel. * cAge
bAge accel. * cAge²
Hannum	bAge accel.
bAge accel. * cAge
bAge accel. * cAge²
2nd generation Epigenetic Aging Clocks		Levine	bAge accel.
bAge accel. * cAge
bAge accel. * cAge²
GrimAge	bAge accel.
bAge accel. * cAge
bAge accel. * cAge²
Inflammatory Aging Clock		iAge	bAge accel.
bAge accel. * cAge
bAge accel. * cAge²

I struggle quite a bit automatizing the process. Could someone help me?

Any help will be greatly appreciated. Thank you all !

Cheers,
Laure

[laure rouch]

Dear All,

For more clarity, I am attaching the kind of table I would like to create example.xlsx

Thank you so much Stata community for your help!

Cheers,
Laure

[Rich Goldstein]

please read and follow the advice in the FAQ - I, and many others, will not open binary files from people we don't know; also, the use of CODE blocks will make what you post much easier to read; finally, see

h etable

[laure rouch]

Hello Rich, thank you. I have updated my code as follows.

mixed score age##age##c.dnamgrimage_adjage_10 i.sexep || id: age
mixed score age##age##c.ageacc3_horvath_10 i.sexep || id: age
mixed score age##age##c.ageacc3_skinhorvath_10 i.sexep || id: age
mixed score age##age##c.ageacc3_hannum_10 i.sexep || id: age
mixed score age##age##c.ageacc3_levine_10 i.sexep || id: age
mixed score age##age##c.ageacc_iage_10 i.sexep || id: age

According to the forum guidelines, I am attaching an image of the table I want to create. As recommended I have uploaded it in .png format.

Please let me know if this helps and I do appreciate your time and help.

Best,
Laure

[laure rouch]

Hi all,

I was wondering if anyone could help me to extract the coefficients (95% CI) and p-value for each model in a table?

I would just need a table (that I will customize later myself) with coefficients (95% CI) and p value for (1) c.dnamgrimage_adjage_10 effect (2) c.dnamgrimage_adjage_10*age and (3) dnamgrimage_adjage*age*age. And same thing for the other 5 models (horvath, skin horvath, hannum, levine and iage). I don't need a fancy table, just extract the coefficients.

Thank you so much, any help would be greatly appreciated.

Best wishes,
Laure

  
 mixed score age##age##c.dnamgrimage_adjage_10 i.sexep || id: age mixed score age##age##c.ageacc3_horvath_10 i.sexep || id: age mixed score age##age##c.ageacc3_skinhorvath_10 i.sexep || id: age mixed score age##age##c.ageacc3_hannum_10 i.sexep || id: age mixed score age##age##c.ageacc3_levine_10 i.sexep || id: age mixed score age##age##c.ageacc_iage_10 i.sexep || id: age

[Andrew Musau]

As Rich Goldstein stated in #3, you should follow the FAQ advice and include a reproducible example that allows us to run some of your regressions. Currently, you’ve presented a set of commands without a data example. If you’ve already created the basic structure of the table in Excel, you can populate the corresponding cells with results using putexcel as you run the regressions. See:

help putexcel

Your ideal reproducible example should include the following:

1. Create the structure of the table for at least 2 regressions in Excel. Then use import excel to import the Excel file to Stata. Run dataex and post the output here.
2. Post a data example for at least 2 regressions.

[Jeff Pitblado (StataCorp)]

Without providing data to go with your Stata code, you severely limit your chances of getting help with this. If the following does not align with what you want to do, please follow Andrew Musau's advice to improve your changes of getting helpful responses.

If you are using Stata 16 or older, look at matrix result r(table). It's columns correspond with those of matrix e(b); the rows of r(table) include the coefficient estimates, their CI limits, and p-values.

If you have Stata 17 or newer, you can use command etable (as suggested by Rich Goldstein), followed up with some collect commands to style and arrange the estimation results of interest, then publish the resulting table to MS Excel.

In the following, I use Stata 17 and simulated data loosely suggested by your Stata code and table image.

* simulate some data
set seed 20250425
set obs 300
generate id = _n
generate u1 = rnormal()
generate u2 = rnormal()
expand 10
generate age = runiformint(24, 60)
generate dnamgrimage = 0.24 + 0.82 * runiform()
generate horvath = 0.24 + 0.82 * runiform()
generate skin = 0.24 + 0.82 * runiform()
generate hannum = 0.24 + 0.82 * runiform()
generate levine = 0.24 + 0.82 * runiform()
generate iage = 0.24 + 0.82 * runiform()
generate sex = runiformint(0, 1)
label define sex 0 "Male" 1 "Female"
generate double score = 10 + 5*sex ///
        + dnamgrimage ///
        + horvath ///
        + skin ///
        + hannum ///
        + levine ///
        + iage ///
        + age/10 ///
        + age*age/50 ///
        + u1 + u2*age + rnormal()

* lists of covariates of interest
unab list : dnamgrimage horvath skin hannum levine iage

* Fit a model using each covariate of interest, but use a commonly named
* copy to simplify selecting and labeling the coefficients of interest.
* Name the estimates after the variable of interest to simplify the call to
* -etable-.
foreach x of local list {
        clonevar X = `x'
        mixed score c.age##c.age##c.X i.sex || id: age
        drop X
        estimates store `x'
}

* use -etable- to collect results from the above stored estimates
etable, estimates(`list') ///
        column(estimates) ///
        cstat(_r_b) ///
        cstat(_r_p, minimum(.001) nformat(%5.3f)) ///
        mstat(none)

* clear stars results since we are showing p-values
collect stars, clear

* custom result for CIs
collect composite define _r_ci = _r_lb _r_ub, trim delimiter("; ")
collect style cell result[_r_ci], sformat("(%s)")

* show these result labels in the header, -etable- hides them by default
collect style header result[_r_b _r_ci _r_p], level(label)

* define new tags for row header groups corresponding to the biological
* aging clocks
collect label list etable_estimates
collect addtags clock[Horvath], fortags(etable_estimates[2])
collect addtags clock["Skin Horvath"], fortags(etable_estimates[3])
collect addtags clock[Hannum], fortags(etable_estimates[4])
collect addtags gclock["1st generation Epigenetic Aging Clocks"], ///
        fortags(etable_estimates[2 3 4])
collect addtags clock[Levine], fortags(etable_estimates[5])
collect addtags clock[GrimAge], fortags(etable_estimates[1])
collect addtags gclock["2st generation Epigenetic Aging Clocks"], ///
        fortags(etable_estimates[5 1])
collect addtags clock[iAge], fortags(etable_estimates[6])
collect addtags gclock["Inflammatory Aging Clock"], ///
        fortags(etable_estimates[6])

* identify coefficients of interest
collect style autolevels colname X X#age X#age#age , clear

* label coefficients of interest as you like
* note: ² is unicode character (u00b2)
collect label levels colname ///
        X "bAage accel." ///
        X#age "bAge accel. * cAge" ///
        X#age#age "bAge accel. * cAge²" ///
        , modify

* style the row headers according to the image provided
collect style row split, dups(center)

* arrange the results according to the image provided
collect layout (gclock#clock#colname) (result[_r_b _r_ci _r_p])

* publish to MS Excel
collect export table.xlsx, replace

Here is a screenshot of the table in LibreOffice on my Mac.

[laure rouch]

Thank you so much, Jeff for your valuable time! I do appreciate it.

Cheers, Laure