Problem generating regression results in a particular format

[Kehinde Atoloye]

I am trying to run multiple regressions with the following output, but my efforts are to no avail. Kindly assist. thanks.

Here is the output format I am targeting.

| Covariate | Outcome1               | Outcome2               | Outcome3               | Outcome4               |
|-----------|------------------------|------------------------|------------------------|------------------------|
| cov1      | 1.23 [CI: 1.10, 1.38]* | 1.35 [CI: 1.12, 1.63]* | 0.89 [CI: 0.78, 1.01]  | 1.11 [CI: 0.95, 1.30]  |
| cov2      | 0.75 [CI: 0.60, 0.92]* | 0.88 [CI: 0.74, 1.05]  | 1.45 [CI: 1.21, 1.74]* | 0.98 [CI: 0.80, 1.19]  |
| cov3      | 1.12 [CI: 0.95, 1.31]  | 0.79 [CI: 0.67, 0.94]* | 1.02 [CI: 0.85, 1.22]  | 0.91 [CI: 0.73, 1.14]  |
| cov4      | 0.89 [CI: 0.73, 1.08]  | 1.15 [CI: 0.95, 1.38]  | 0.93 [CI: 0.78, 1.12]  | 1.08 [CI: 0.90, 1.30]  |

Here is my data.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte area float agecat byte sex float(educat occupcat married) byte sexever float(sexdebut_cat mdp mh2cat mh7ab2 mh17cat mh10cat)
2 1 1 3 2 2 1 1 . 0 . . .
2 2 2 4 2 1 1 3 0 1 1 0 1
2 3 2 3 1 1 1 4 . 0 1 0 1
2 1 2 3 2 2 0 . . 1 1 0 1
2 1 2 3 1 2 1 1 . 0 1 0 1
2 1 2 2 2 1 1 1 1 0 0 0 1
2 2 1 3 1 2 0 . . 0 . . .
2 3 1 3 2 2 0 . 1 0 . . .
2 2 1 4 2 1 1 3 0 0 . . .
2 3 1 4 2 1 1 3 . 0 . . .
2 2 1 4 3 2 1 3 0 0 . . .
2 1 1 3 3 2 1 3 0 0 . . .
2 3 1 3 2 1 1 3 . 0 . . .
2 3 1 3 2 1 1 3 . 0 . . .
2 3 1 3 2 1 1 3 . 0 . . .
2 1 1 3 3 2 0 . . 0 . . .
2 2 1 3 2 2 1 3 . 0 . . .
2 1 1 3 3 2 0 . . 0 . . .
2 3 1 4 2 1 1 3 . 0 . . .
2 2 1 3 3 2 0 . 0 0 . . .
2 2 1 4 2 2 1 3 0 0 . . .
2 2 1 3 2 2 1 3 . 0 . . .
2 1 1 3 2 2 0 . . 0 . . .
2 2 1 4 2 1 1 3 1 0 . . .
2 1 1 3 2 2 0 . . 0 . . .
2 1 1 3 2 2 0 . . 0 . . .
2 1 1 3 2 2 0 . . 0 . . .
2 3 1 4 2 1 1 3 . 0 . . .
2 2 1 3 2 2 0 . . 0 . . .
2 2 1 3 2 2 1 3 0 0 . . .
2 1 1 4 2 2 1 2 1 0 . . .
2 1 1 4 3 2 0 . 0 0 . . .
2 1 1 3 3 2 0 . 1 0 . . .
2 1 1 3 3 2 0 . . 0 . . .
2 1 1 3 2 2 0 . 0 0 . . .
2 1 1 3 2 2 0 . . 0 . . .
2 1 1 3 3 2 0 . . 0 . . .
2 1 1 3 3 2 0 . . 0 . . .
2 2 2 4 1 2 0 . 1 0 0 1 1
2 3 2 3 2 1 1 3 1 0 1 0 1
2 2 2 3 3 1 1 3 1 0 1 0 1
2 1 2 3 3 2 0 . 1 0 0 0 1
2 2 2 3 2 1 1 3 0 0 0 0 1
2 1 2 3 3 2 0 . 1 0 0 1 1
2 3 2 3 2 1 1 3 1 0 0 0 1
2 2 2 4 3 2 0 . 0 1 1 0 1
2 2 2 4 3 2 0 . 0 0 1 0 1
2 2 2 2 2 2 1 1 1 0 1 1 0
2 2 2 3 3 1 1 1 1 0 0 1 1
2 1 2 2 2 2 1 1 1 1 1 1 1
2 3 2 4 2 2 0 . 0 0 1 1 1
2 3 2 3 3 1 1 2 1 0 0 1 1
2 1 2 1 3 2 0 . 1 0 1 0 1
2 2 2 3 3 1 1 3 1 1 0 1 1
2 3 2 3 2 1 1 1 1 0 0 1 0
2 1 2 4 3 2 0 . 1 0 1 0 1
2 1 2 3 3 1 1 1 1 0 0 1 1
2 1 2 3 3 2 1 2 1 0 1 0 1
2 1 2 4 3 1 1 1 1 0 0 0 1
2 3 2 4 3 1 1 1 0 0 0 0 1
2 1 2 3 2 2 0 . 1 0 0 0 1
2 1 2 3 2 2 0 . 0 1 0 0 1
2 3 2 3 2 1 1 1 . 0 1 1 1
2 1 2 1 3 2 1 1 0 0 0 1 1
2 1 2 1 3 1 1 1 1 0 0 1 1
2 1 2 4 3 2 0 . 0 1 0 0 1
2 1 2 4 3 2 0 . 1 0 0 1 1
2 1 2 3 1 2 1 2 1 0 1 0 1
2 3 2 3 1 1 1 3 0 1 1 0 1
2 1 1 3 1 2 1 1 . 0 . . .
2 2 2 3 1 1 1 2 1 0 1 0 1
2 2 2 2 2 1 1 3 0 0 1 0 1
2 2 1 4 2 1 1 3 . 0 . . .
2 2 2 1 . 1 1 2 . 0 0 0 1
2 1 2 1 . 1 1 2 . 1 1 1 0
2 2 2 2 1 1 1 1 1 0 0 0 1
2 3 1 3 1 1 1 3 1 0 . . .
2 1 2 3 1 2 1 1 . 1 1 0 1
2 1 2 3 1 1 1 1 1 1 0 0 1
2 2 1 2 2 1 1 1 . 0 . . .
2 2 1 2 2 1 1 1 1 0 . . .
2 2 1 2 2 1 1 3 . 0 . . .
2 2 2 2 1 1 1 3 1 0 0 0 1
2 1 2 2 1 1 1 1 . 0 0 0 1
2 1 2 1 1 1 1 1 1 0 0 0 1
2 1 1 2 1 2 1 1 . 0 . . .
2 1 2 2 1 2 0 . . 0 0 0 0
2 3 1 4 1 1 1 3 1 0 . . .
2 2 2 4 1 1 1 2 . 0 0 0 0
2 1 1 2 1 2 1 1 1 0 . . .
2 1 1 2 1 2 0 . 1 0 . . .
2 1 1 1 . 2 1 1 . 0 . . .
2 1 1 2 1 2 0 . 1 0 . . .
2 1 1 2 1 2 0 . 1 0 . . .
2 2 1 4 1 2 1 1 . 0 . . .
2 3 2 2 1 2 1 2 1 0 1 0 1
2 1 1 4 1 2 1 1 . 0 . . .
2 3 2 1 1 1 1 3 1 0 0 1 0
2 3 1 2 1 1 1 3 1 0 . . .
2 2 2 3 2 1 1 2 0 0 0 0 1
end
label values area area
label def area 2 "2. Urban", modify
label values agecat agecat
label def agecat 1 "1. 18-19", modify
label def agecat 2 "2. 20-35", modify
label def agecat 3 "3. 36-49", modify
label values sex sex
label def sex 1 "1. Male", modify
label def sex 2 "2. Female", modify
label values educat educat
label def educat 1 "None/Non-formal", modify
label def educat 2 "Primary", modify
label def educat 3 "Secondary", modify
label def educat 4 "Tertiary", modify
label values occupcat occupcat
label def occupcat 1 "Unemployed", modify
label def occupcat 2 "Employed", modify
label def occupcat 3 "Homemaker/Student", modify
label values married married
label def married 1 "1. Married", modify
label def married 2 "2. Unmarried", modify
label values sexever yesno
label values mdp yesno
label def yesno 0 "0. No", modify
label def yesno 1 "1. Yes", modify
label values sexdebut_cat sexdebut_cat
label def sexdebut_cat 1 "1. Less than 18", modify
label def sexdebut_cat 2 "2. 18-19", modify
label def sexdebut_cat 3 "3. 20-36", modify
label def sexdebut_cat 4 "4. 36 and above", modify
label values mh2cat mh2cat
label def mh2cat 0 "0. Poor Knowledge", modify
label def mh2cat 1 "1. Good Knowledge", modify
label values mh7ab2 mh7ab2
label def mh7ab2 0 "0. Non-favourable attitude", modify
label def mh7ab2 1 "1. Favourable attitude", modify
label values mh17cat mh17cat
label def mh17cat 0 "0. Negative practices", modify
label def mh17cat 1 "1. Positve practices", modify
label values mh10cat mh10cat
label def mh10cat 0 "0. Low quality materials", modify
label def mh10cat 1 "1. High quality materials", modify

Here is the code I attempted

* Define the outcomes and covariates
local outcomes mh2cat mh7ab2 mh17cat mh10cat  // Replace with your actual outcome variables
local covariates area agecat sex educat occupcat married sexever sexdebut_cat mdp  // Replace with your actual covariates

set trace on

* Set up the Excel file and add headers
putexcel set newresults.xlsx, replace
putexcel A1 = "Covariate"  // Covariate names in the first column

* Add outcome names as column headers
local col = 2
foreach y of local outcomes {
    local cell = "`=char(`col' + 64)'1"  // Construct valid cell name (e.g., B1, C1)
    putexcel `cell' = "`y'"
    local col = `col' + 1
}

* Initialize row counter for covariates
local row = 2

* Loop over covariates
foreach x of local covariates {
    * Check if dummy variables already exist and drop them
    capture drop dummy_*
    
    * Generate dummy variables for categorical covariates
    quietly tabulate `x', generate(dummy_)

    foreach dummyvar of varlist dummy_* {
        * Write dummy variable names row-wise
        putexcel A`row' = "`dummyvar'"

        local col = 2  // Initialize column counter for outcomes
        foreach y of local outcomes {
            quietly logit `y' i.`covariates'
            matrix M = r(table)
            
            * Get column names of the matrix
            local ncols = colsof(M)  // Number of columns in the matrix
            local colnames ""
            forval i = 1/`ncols' {
                local colnames "`colnames' " + "`=colname(M, `i')'"
            }

            * Find the index of the current dummy variable in the matrix
            local idx = .  // Default to missing
            foreach colname of local colnames {
                if "`colname'" == "`dummyvar'" {
                    local idx = `i'
                    break
                }
            }

            * Extract coefficients, confidence intervals, and p-value
            if `idx' != . {
                local OR = el(M, 1, `idx')             // Odds ratio
                local lowerCI = el(M, 5, `idx')        // Lower confidence interval
                local upperCI = el(M, 6, `idx')        // Upper confidence interval
                local pval = el(M, 4, `idx')           // P-value
                
                * Check significance
                local significance = ""
                if `pval' < 0.05 {
                    local significance = "*"
                }
                
                * Combine coefficients and CIs into a single string
                local result = "`=round(`OR', 2)' [CI: `=round(`lowerCI', 2)', `=round(`upperCI', 2)']`significance'"
                
                * Write results to Excel
                local cell = "`=char(`col' + 64)'`row''"  // Construct valid cell name
                putexcel `cell' = "`result'"
            } 
            else {
                * Handle the case where the dummy variable was not found
                putexcel `cell' = "Not found"
            }
            local col = `col' + 1
        }
        local row = `row' + 1
    }
    drop dummy_*  // Clean up generated dummy variables
}

[Andrew Musau]

Rather than attempting to program this, I would use a canned program. The following uses estout from SSC. You can output as a CSV file and then open in Excel.

sysuse auto, clear
local outcomes mpg price
local regressors disp weight turn gear

estimates clear
foreach outcome of local outcomes{
    eststo: regress `outcome' `regressors', robust
}
esttab est*, aux(ci 2) main(b 2) staraux wide label varwidth(25)

Res,:

. esttab est*, aux(ci 2) main(b 2) staraux wide label varwidth(25)

-----------------------------------------------------------------------------------
                                   (1)                          (2)                
                          Mileage (m~)                        Price                
-----------------------------------------------------------------------------------
Displacement (cu. in.)            0.01 [-0.01,0.03]           10.27 [-3.74,24.29]   
Weight (lbs.)                    -0.01 [-0.01,-0.00]***         3.65  [1.20,6.10]** 
Turn circle (ft.)                -0.13 [-0.44,0.17]         -373.54 [-707.78,-39.31]*  
Gear ratio                        0.57 [-2.94,4.08]         1968.90 [-78.40,4016.20]   
Constant                         41.26 [26.88,55.64]***      1996.89 [-11722.75,15716.52]   
-----------------------------------------------------------------------------------
Observations                        74                           74                
-----------------------------------------------------------------------------------
b coefficients; ci in brackets
* p<0.05, ** p<0.01, *** p<0.001

[Kehinde Atoloye]

Thank you. I actually want the regressions to be unadjusted using a logistic regression. Your recommended approach is adjusted.

[Andrew Musau]

If you want to append univariate regressions to output with esttab, you can reach for appendmodels by Ben Jann. Search the forum for mentions. I can illustrate later in the day.

[Kehinde Atoloye]

Thank you very much. Will be glad to see your illustration.

[Andrew Musau]

The idea below is to save the first estimate involving the outcome mpg as 'mpg1,' the second as 'mpg2,' and so on. The same approach applies to the outcome price. This makes it easy to append these results using the outcome names, mpg and price in this case. The appendmodels command accomplishes this (see the code below). Lastly, it is important to clarify that these are appended univariate regressions. Otherwise, the format might resemble that of multivariate regressions, which could mislead your readers if not explicitly stated. Note that the number of observations will differ between groups if there are missing values, so you may want to start with a common estimation sample or report the number of observations for each univariate regression. See my modification of appendmodels (titled appendmodelsobs) that will allow you to report observations for each regression in #8 of https://www.statalist.org/forums/for...ally-in-3-rows. Whether you are estimating linear regression or logistic regression is irrelevant to the illustration below, as both work well. Run the code in a do-file.

sysuse auto, clear
local outcomes mpg price
estimates clear
foreach outcome of local outcomes{
    local m 0
    local `outcome'
    foreach regressor in disp weight turn gear{
        local ++m
        eststo `outcome'`m': regress `outcome' `regressor', robust
        local `outcome' ``outcome'' `outcome'`m'
    }
}

*PROGRAM TO APPEND MODELS
capt prog drop appendmodels
*! version 1.0.0  14aug2007  Ben Jann
program appendmodels, eclass
    // using first equation of model
    version 8
    syntax namelist
    tempname b V tmp
    foreach name of local namelist {
        qui est restore `name'
        mat `tmp' = e(b)
        local eq1: coleq `tmp'
        gettoken eq1 : eq1
        mat `tmp' = `tmp'[1,"`eq1':"]
        local cons = colnumb(`tmp',"_cons")
        if `cons'<. & `cons'>1 {
            mat `tmp' = `tmp'[1,1..`cons'-1]
        }
        mat `b' = nullmat(`b') , `tmp'
        mat `tmp' = e(V)
        mat `tmp' = `tmp'["`eq1':","`eq1':"]
        if `cons'<. & `cons'>1 {
            mat `tmp' = `tmp'[1..`cons'-1,1..`cons'-1]
        }
        capt confirm matrix `V'
        if _rc {
            mat `V' = `tmp'
        }
        else {
            mat `V' = ///
            ( `V' , J(rowsof(`V'),colsof(`tmp'),0) ) \ ///
            ( J(rowsof(`tmp'),colsof(`V'),0) , `tmp' )
        }
    }
    local names: colfullnames `b'
    mat coln `V' = `names'
    mat rown `V' = `names'
    eret post `b' `V'
    eret local cmd "whatever"
end

*APPEND THE MODELS
foreach outcome of local outcomes{
    eststo `outcome': appendmodels ``outcome''
} 

*OUTPUT
esttab `outcomes', aux(ci 2) main(b 2) mlab(`outcomes', lhs(Variables)) noobs staraux wide label varwidth(25)

Res.:

Linear regression                               Number of obs     =         74
                                                F(1, 72)          =      65.71
                                                Prob > F          =     0.0000
                                                R-squared         =     0.4979
                                                Root MSE          =     4.1278

------------------------------------------------------------------------------
             |               Robust
         mpg | Coefficient  std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
displacement |  -.0444536    .005484    -8.11   0.000    -.0553858   -.0335214
       _cons |   30.06788   1.332856    22.56   0.000     27.41088    32.72488
------------------------------------------------------------------------------

Linear regression                               Number of obs     =         74
                                                F(1, 72)          =     105.83
                                                Prob > F          =     0.0000
                                                R-squared         =     0.6515
                                                Root MSE          =     3.4389

------------------------------------------------------------------------------
             |               Robust
         mpg | Coefficient  std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
      weight |  -.0060087   .0005841   -10.29   0.000     -.007173   -.0048443
       _cons |   39.44028    1.98832    19.84   0.000     35.47664    43.40393
------------------------------------------------------------------------------

Linear regression                               Number of obs     =         74
                                                F(1, 72)          =      80.26
                                                Prob > F          =     0.0000
                                                R-squared         =     0.5172
                                                Root MSE          =     4.0477

------------------------------------------------------------------------------
             |               Robust
         mpg | Coefficient  std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
        turn |  -.9457877   .1055702    -8.96   0.000    -1.156238   -.7353373
       _cons |    58.7965   4.496292    13.08   0.000     49.83331     67.7597
------------------------------------------------------------------------------

Linear regression                               Number of obs     =         74
                                                F(1, 72)          =      38.31
                                                Prob > F          =     0.0000
                                                R-squared         =     0.3797
                                                Root MSE          =     4.5882

------------------------------------------------------------------------------
             |               Robust
         mpg | Coefficient  std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
  gear_ratio |   7.812835   1.262307     6.19   0.000     5.296472     10.3292
       _cons |  -2.257346   3.618892    -0.62   0.535    -9.471475    4.956784
------------------------------------------------------------------------------

Linear regression                               Number of obs     =         74
                                                F(1, 72)          =      19.09
                                                Prob > F          =     0.0000
                                                R-squared         =     0.2450
                                                Root MSE          =     2580.6

------------------------------------------------------------------------------
             |               Robust
       price | Coefficient  std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
displacement |   15.89588   3.637774     4.37   0.000     8.644111    23.14765
       _cons |   3029.042   646.0567     4.69   0.000     1741.152    4316.933
------------------------------------------------------------------------------

Linear regression                               Number of obs     =         74
                                                F(1, 72)          =      27.51
                                                Prob > F          =     0.0000
                                                R-squared         =     0.2901
                                                Root MSE          =     2502.3

------------------------------------------------------------------------------
             |               Robust
       price | Coefficient  std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
      weight |   2.044063   .3897465     5.24   0.000     1.267117    2.821008
       _cons |  -6.707353   1032.394    -0.01   0.995    -2064.747    2051.332
------------------------------------------------------------------------------

Linear regression                               Number of obs     =         74
                                                F(1, 72)          =       7.76
                                                Prob > F          =     0.0068
                                                R-squared         =     0.0959
                                                Root MSE          =       2824

------------------------------------------------------------------------------
             |               Robust
       price | Coefficient  std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
        turn |   207.5794   74.53853     2.78   0.007     58.98958    356.1693
       _cons |  -2064.987   2815.986    -0.73   0.466    -7678.553    3548.579
------------------------------------------------------------------------------

Linear regression                               Number of obs     =         74
                                                F(1, 72)          =       5.38
                                                Prob > F          =     0.0232
                                                R-squared         =     0.0984
                                                Root MSE          =       2820

------------------------------------------------------------------------------
             |               Robust
       price | Coefficient  std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
  gear_ratio |  -2027.544   874.2746    -2.32   0.023    -3770.379   -284.7092
       _cons |   12278.03   2775.365     4.42   0.000     6745.439    17810.62
------------------------------------------------------------------------------

. 
. *OUTPUT
. esttab `outcomes', aux(ci 2) main(b 2) mlab(`outcomes', lhs(Variables)) noobs staraux wide label varwidth(25)

-----------------------------------------------------------------------------------
                                   (1)                          (2)                
Variables                          mpg                        price                
-----------------------------------------------------------------------------------
Displacement (cu. in.)           -0.04 [-0.06,-0.03]***        15.90 [8.77,23.03]***
Weight (lbs.)                    -0.01 [-0.01,-0.00]***         2.04  [1.28,2.81]***
Turn circle (ft.)                -0.95 [-1.15,-0.74]***       207.58 [61.49,353.67]** 
Gear ratio                        7.81 [5.34,10.29]***     -2027.54 [-3741.09,-314.00]*  
-----------------------------------------------------------------------------------
b coefficients; ci in brackets
* p<0.05, ** p<0.01, *** p<0.001

[Kehinde Atoloye]

Thank you very much for the illustration. I understand that it works for either linear regression or logistic regression, but does it work for categorical regressors? I have not tested it though. Thanks.

[Andrew Musau]

Both logit and regress handle categorical regressors.