How to add star to show the statistical significance of coefficient in regression matrix output

[Xinghuan Luo]

Hi, Statalists,

I have got a question on adding star after coefficient in the matrix output of a regression. Below are the code and output.

set trace on
version 15.1
clear
sysuse auto.dta
local outcome price mpg headroom
local k = 1
foreach var of varlist `outcome' {
    qui reg `var ' i.foreign, robust
    mat b_`k' = r(table)
    mat b_`k' = (b_`k'[1, 2..3] \ b_`k'[4, 2..3])'
    local lbl_var: var label `var'
    mat colnames b_`k' = "`lbl_var'" "`lbl_var'_pvalue"
    local val_lbls
    local val_lbl: label(foreign) 1
    local val_lbls `" `val_lbls' "`val_lbl'" "'
    local val_lbls `" `val_lbls' "The Constant" "'
    macro list _val_lbls
    mat rownames b_`k' = `val_lbls'
    local k = `k' + 1
}
mat final = b_1
forvalues k = 2/3 {
    mat final = final, b_`k'
}
mat list final

final[2,6]
                     Price  Price_pvalue  Mileage(mpg)  Mileage (m~e  Headroom (~)  Headroom (~e
     Foreign     312.25874     .65768953     4.9458042      .0020303    -.54020979     .00150673
The Constant     6072.4231     2.258e-22     19.826923     1.881e-42     3.1538462     6.313e-37

There are three outcome variables, "price" "mpg" "headroom" and the independent variable is foreign which is a categorical variable. The first column of each outcome variable is the coefficient and the second column is the p-value.

Now, the question is that, since I manually add these values to this matrix, there is no star after each coefficient show if this is statistically significant. Ideally, I hope I could add stars to each coefficient. It will be like, if p-value is less than 0.01, then there will be three stars in the top right corner of that coefficient. I need to use a matrix here for later, so I cannot use outreg2 or esttab.

Does anyone happen to know how to achieve this?

Thanks a lot!

[Nick Cox]

The short answer is that matrices in Stata allow numeric entries only. So matrix commands are a dead end here.

A more positive answer is that there are many other ways to do it. You're aware of two community-contributed commands, and there are others, and I can't advise which is best for you for this purpose.

As you want to keep this matrix for later use, you will need to fork, to work on a copy and add stars as well as using this directly.

[Xinghuan Luo]

The short answer is that matrices in Stata allow numeric entries only. So matrix commands are a dead end here.

A more positive answer is that there are many other ways to do it. You're aware of two community-contributed commands, and there are others, and I can't advise which is best for you for this purpose.

As you want to keep this matrix for later use, you will need to fork, to work on a copy and add stars as well as using this directly.

Thank you Nick! I just have one follow-up question.

version 15.1
clear 
sysuse auto.dta
recode mpg min/22 = 0 23/max = 1, gen(mpg_factor)
label define mpg_lab 0 "below 22" 1 "aobve 22" 
label val mpg_factor mpg_lab
local outcome price mpg headroom
forvalues j = 1/2 {
    if `j' == 1 {
        local idpdt_var foreign
    }
    else if `j' == 2 {
        local idpdt_var mpg_factor
    }

    local k = 1 
    foreach var of varlist `outcome'  {
        qui reg `var ' i.`idpdt_var', robust 
        mat b_`k' = r(table)
        mat b_`k' = (b_`k'[1, 2..3] \ b_`k'[4, 2..3])'
        local lbl_var: var label `var'
        mat colnames b_`k' = "`lbl_var'" "`lbl_var'_se"
        local val_lbls 
        local val_lbl: label(`idpdt_var') 1
        local val_lbls `" `val_lbls' "`val_lbl'" "'
        local val_lbls `" `val_lbls' "The Constant" "'
        macro list _val_lbls
        mat rownames b_`k' = `val_lbls'
        local k = `k' + 1
    }
    mat final_`j' = b_1
    forvalues k = 2/3 {
        mat final_`j' = final_`j', b_`k'
    }
}

mat final_matrix = final_1\final_2
mat list final_matrix

final_matrix[4,6]
                     Price      Price_se  Mileage(mpg)  Mileage (m~e  Headroom (~)  Headroom (~e
     Foreign     312.25874     .65768953     4.9458042      .0020303    -.54020979     .00150673
The Constant     6072.4231     2.258e-22     19.826923     1.881e-42     3.1538462     6.313e-37
    aobve 22     -1799.758     .00195786     9.6810897     2.828e-15    -.81971154     6.589e-06
The Constant     6797.6042     3.261e-22     17.895833     1.235e-54       3.28125     3.913e-40

Actually, as you can see here, there are two different groups of independent variable in the final output, "foreign" and "mpg_factor". The first two rows of this matrix are the categorial regression on foreign and the last two rows are the regression on mpg_fator. They are different regression.

I guess I cannot make such a regression table by outreg2 or esttab in a sense see that I can only add the regression model one column by one column. And if there is an independent variable that is not included in the previous model, the not-included variables will just be empty value in their position. This does not look good.

Do you happen to know any command allowing me to create such a regression table efficiently? Thanks.

[Nick Cox]

Sorry, no. I don't use any of these commands.

[Al Perez]

Hi Xinghuan,

Any luck? I have a similar need for storing regression coefficients with stars, along with se and N. I need to export a decent chunk of regressions to a customized excel file that, unfortunately, is a format I cannot change. So far, I've been storing estimates in a 3 x 25 matrix and exporting to an excel file using putexcel. Each independent variable I regress on gets its own matrix.

foreach subj in in math ela {

putexcel set "`subj'.xls"

      foreach outcome in a b c d {

          forval year = 2005/2010 {
          
                forval h = 2/6 {
               
                     reg `outcome' x1 x2 3 c.subjscore_centered##1.pass_`subj' if year == `year' & inrange(`subj'score_centered, -`h' , `h')
                     mat A`h' = (_b[1.pass_`subj'] \ _se[1.pass_`subj'] \ e(N))

                }
               mat B`year' = ( A2, A3, A4, A5, A6) 
          }
           mat C`outcome' = (B2005, B2006, B2007, B2008, B2009, B2010)
     } 
     putexcel B103 = mat(Ca)
     putexcel B107 = mat(Cb)
     putexcel B111 = mat(Cc)
     putexcel B115 = mat(Cd)
}

But as Nick mentioned above, I wouldn't be able to store results with significant stars in a matrix. Is there a way to store estimates with stars? Exactly like esttab but I want to somehow store that output and export it to a specific cell in an existing excel file.

Apologies if this is a weird query. I'm not familiar with Stata programming. Thanks!

[Xinghuan Luo]

Hi Xinghuan,

Any luck? I have a similar need for storing regression coefficients with stars, along with se and N. I need to export a decent chunk of regressions to a customized excel file that, unfortunately, is a format I cannot change. So far, I've been storing estimates in a 3 x 25 matrix and exporting to an excel file using putexcel. Each independent variable I regress on gets its own matrix.

foreach subj in in math ela {

putexcel set "`subj'.xls"

foreach outcome in a b c d {

forval year = 2005/2010 {

forval h = 2/6 {

reg `outcome' x1 x2 3 c.subjscore_centered##1.pass_`subj' if year == `year' & inrange(`subj'score_centered, -`h' , `h')
mat A`h' = (_b[1.pass_`subj'] \ _se[1.pass_`subj'] \ e(N))

}
mat B`year' = ( A2, A3, A4, A5, A6)
}
mat C`outcome' = (B2005, B2006, B2007, B2008, B2009, B2010)
}
putexcel B103 = mat(Ca)
putexcel B107 = mat(Cb)
putexcel B111 = mat(Cc)
putexcel B115 = mat(Cd)
}

But as Nick mentioned above, I wouldn't be able to store results with significant stars in a matrix. Is there a way to store estimates with stars? Exactly like esttab but I want to somehow store that output and export it to a specific cell in an existing excel file.

Apologies if this is a weird query. I'm not familiar with Stata programming. Thanks!

Hi Al,

Sorry I did not figure out how to do it finally....

[Attaullah Shah]

This is rather an un-usual table. asdoc (from SSC) can create this with the option row. However, asdocx (currently available for a nominal fee of $9.99) is better placed to do it using its flexmat capability. For more details on asdocx, please visit https://fintechprofessor.com/asdocx/ In the following lines, I show how to create this table. The code might look intimidating, however, if this table is something which is needed on frequent basis, one can easily create a template file from the code and just plug-in the template to asdocx, then this whole code would be reduced to a single line.

The point of this post is that asdocx has an internal engine of creating flexible matrices (flexmat), which can store numbers and string. These matrices can then be written to MS Word, MS Excel or LaTeX format. asdocx follows the same syntax as asdoc, however, it creates these flexible matrices internally, which are then available to users for further processing. Each row and column of these matrices can be accessed and modified.

clear
sysuse auto.dta
recode mpg min/22 = 0 23/max = 1, gen(mpg_factor)
label define mpg_lab 0 "below 22" 1 "aobve 22"
label val mpg_factor mpg_lab
local outcome price mpg headroom

* Make a program for stars
cap prog drop makestars
program define makestars, rclass

      if `1'<= 0.01 {
          local star "***"
      }
      else if `1'<=0.05{
      local star "**"
      }
      else if `1'<=0.1{
      local star "*"
      }
      else {
      local star " "
      }
      return local star `star'
  end



* Write the first row of the table
* Please note the option row(1) and col(1). These options mark the starting row and column where the data will be written. 
asdocx reset
asdocx addrow, data(Variables, Price, Price_se, Mileage(mpg),  Mileage_se,  Headroom,  Headroom_Se) row(1) col(1)

* Run the first regression
reg price i.foreign

local lbl_var: var label foreign

mat rtable = r(table)
loc cof    = round(rtable[1,2], .005)
loc cons   = round(rtable[1,3], .005)
loc cof_p  = rtable[4,2]
loc cons_p = rtable[4,3]

makestars `cof_p'
loc cof `cof'`r(star)'

makestars `cons_p'
loc cons `cons'`r(star)'

loc cof_se     = rtable[2,2]
loc cons_se = rtable[2,3]

* Write these macros to the flexmat table
asdocx addrow, data(`lbl_var', `cof', `cof_se') row(2) col(1) dec(3)

* Write Constant errors to the next row
asdocx addrow, data(The Constant, `cons', `cons_se') row(3) col(1) dec(3)

* Repeat the above process for the next regression

reg price i.mpg_factor

local lbl_var: var label mpg_factor

mat rtable     = r(table)
loc cof     = round(rtable[1,2], .005)
loc cons     = round(rtable[1,3], .005)

loc cof_se     = rtable[2,2]
loc cons_se = rtable[2,3]

loc cof_p     = rtable[4,2]
loc cons_p  = rtable[4,3]

makestars `cof_p'
loc cof `cof'`r(star)'

makestars `cons_p'
loc cons `cons'`r(star)'

* Write these macros to the flexmat table
asdocx addrow, data(`lbl_var', `cof', `cof_se') row(4) col(1) dec(3)

* Write Constant errors to the next row
asdocx addrow, data(The Constant, `cons', `cons_se') row(5) col(1) dec(3)

/*------------------------------------------------------------------------------
* Repeat the process for the Mileage Regression
* In the flexmat regression, we shall change to column 4
*=============================================================================*/
reg mpg i.foreign

mat rtable     = r(table)
loc cof     = round(rtable[1,2], .005)
loc cons     = round(rtable[1,3], .005)

loc cof_se     = rtable[2,2]
loc cons_se = rtable[2,3]

loc cof_p     = rtable[4,2]
loc cons_p  = rtable[4,3]

makestars `cof_p'
loc cof `cof'`r(star)'

makestars `cons_p'
loc cons `cons'`r(star)'

* Write these macros to the flexmat table
asdocx addrow, data(`cof', `cof_se') row(2) col(4) dec(3)

* Write Constant errors to the next row
asdocx addrow, data(`cons', `cons_se') row(3) col(4) dec(3)

* Repeat the above process for the next regression

reg mpg i.mpg_factor

mat rtable     = r(table)
loc cof     = round(rtable[1,2], .005)
loc cons     = round(rtable[1,3], .005)

loc cof_se     = rtable[2,2]
loc cons_se = rtable[2,3]

loc cof_p     = rtable[4,2]
loc cons_p  = rtable[4,3]

makestars `cof_p'
loc cof `cof'`r(star)'

makestars `cons_p'
loc cons `cons'`r(star)'


* Write these macros to the flexmat table
asdocx addrow, data(`cof', `cof_se') row(4) col(4) dec(3)

* Write Constant errors to the next row
asdocx addrow, data(`cons', `cons_se') row(5) col(4) dec(3)

/*------------------------------------------------------------------------------
* Repeat the process for the Headroom
* In the flexmat regression, we shall change to column 6
*=============================================================================*/
reg headroom i.foreign

mat rtable     = r(table)
loc cof     = round(rtable[1,2], .005)
loc cons     = round(rtable[1,3], .005)

loc cof_se     = rtable[2,2]
loc cons_se = rtable[2,3]

loc cof_p     = rtable[4,2]
loc cons_p  = rtable[4,3]

makestars `cof_p'
loc cof `cof'`r(star)'

makestars `cons_p'
loc cons `cons'`r(star)'

* Write these macros to the flexmat table
asdocx addrow, data(`cof', `cof_se') row(2) col(6) dec(3)

* Write Constant errors to the next row
asdocx addrow, data(`cons', `cons_se') row(3) col(6) dec(3)

* Repeat the above process for the next regression

reg headroom i.mpg_factor

mat rtable     = r(table)
loc cof     = round(rtable[1,2], .005)
loc cons     = round(rtable[1,3], .005)

loc cof_se     = rtable[2,2]
loc cons_se = rtable[2,3]

loc cof_p     = rtable[4,2]
loc cons_p  = rtable[4,3]

makestars `cof_p'
loc cof `cof'`r(star)'

makestars `cons_p'
loc cons `cons'`r(star)'

* Write these macros to the flexmat table
asdocx addrow, data(`cof', `cof_se') row(4) col(6) dec(3)

* Write Constant errors to the next row
asdocx addrow, data(`cons', `cons_se') row(5) col(6) dec(3)

asdocx export

The internal matrics created by asdocx looks like this

0   |1                              2           3           4           5           6           7 
----+---------------------------------------------------------------------------------------------
  1 |Variables                  Price    Price_seMileage(mpg)  Mileage_se    Headroom Headroom_Se 
----+---------------------------------------------------------------------------------------------
  2 |Car type                 312.260     754.449    4.945***       1.362      -.54**       0.207 
  3 |The Constant         6072.425***     411.363   19.825***       0.743    3.155***       0.113 
  4 |above 22              -1799.76**     691.385     9.68***       0.843  -.82001***       0.184 
  5 |The Constant         6797.605***     409.818   17.895***       0.500     3.28***       0.109 
--------------------------------------------------------------------------------------------------

The rows and column numbers are reported to easily identify cells and modify them, if necessary.

[Xinghuan Luo]

Hi, I just figured out there is no need to use matrix to store the outputs since matrix can only store numeric values. I can use "regsave" and "texsave" to achieve this. The code is here.