Create a loop for regression and generate an output (matrix)

[Mel Shaun]

Hi guys!
I am new to stata and trying to do a loop for a regression for each country (I have 14 countries). I like to generate an output as an matrix and then put it into an excel sheet. This is what I've done, but there occurs an error code:
conformability error
r(503);

putexcel set regresultwithoutweight

foreach i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 {

regress y1 x1 x2 x3 x4 i.YoIDummy if CCDummy== `i', vce(robust) beta
matrix betas=nullmat(beta), e(b)
matrix r2=nullmat(r2), e(r2)
matrix s2=nullmat(s2), e(V)
}

putexcel (A3) = matrix(betas), names overwritefmt
putexcel (A10) = matrix(r2), names overwritefmt
putexcel (A30) = matrix(s2), names overwritefmt

The CCDummy contains the numbering from 1 to 14 which I assigned to each company.

I appreciate any help on this, also if there is an easier way to save just all regression results in that matrix.

Thanks,
Mel

[River Huang]

Hi, Mel: You can use (ssc install) -outreg2- to run regressions, and save the results in Excel.

[River Huang]

An example is as follows:

webuse grunfeld, clear

local replace replace
forvalues i=1(1)10{ 
  reg invest mvalue kstock if company == `i'
  outreg2 using "temp", word excel dec(3) ctitle(`i') `replace'
  local replace append
}

[Mel Shaun]

well, outreg2 is nice but how do I get then the results in the matrix?

[Mel Shaun]

Hi, I actually found some sort of solution but it gets me only the coefficients in a matrix and not the standardized coefficients beta. Do you know how I can get those?

matrix res = J(14,4, .)
matrix colnames res = x1 x2 x3 x4
matrix rownames res = CCDummy1 CCDummy2 CCDummy3 CCDummy4 CCDummy5 CCDummy6 CCDummy7 CCDummy8 CCDummy9 CCDummy10 CCDummy11 CCDummy12 CCDummy13 CCDummy14
local irow=0
foreach i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 {
local ++irow
regress y1 x1 x2 x3 x4 if CCDummy== `i', vce(robust) beta
matrix res[`irow',1] =_b[x1]
matrix res[`irow',2] =_b[x2]
matrix res[`irow',3] =_b[x3]
matrix res[`irow',4] =_b[x4]
}
matrix list res
putexcel (A2) = matrix(res), names overwritefmt

I think there must be another term for _b[variablelist]

[Andrew Musau]

Not so easy if you rely on Stata's saved estimates, but there are tricks...

sysuse auto
reg price mpg weight displacement, b
qui esttab, beta
mat beta= e(beta)
*ALL BETA COEFFICIENTS
mat list beta
*EXTRACT SPECIFIC VALUE
di beta[1,1]

. sysuse auto
(1978 Automobile Data)

. reg price mpg weight displacement, b

      Source |       SS           df       MS      Number of obs   =        74
-------------+----------------------------------   F(3, 70)        =      9.74
       Model |   187000328         3  62333442.8   Prob > F        =    0.0000
    Residual |   448065068        70  6400929.54   R-squared       =    0.2945
-------------+----------------------------------   Adj R-squared   =    0.2642
       Total |   635065396        73  8699525.97   Root MSE        =      2530

------------------------------------------------------------------------------
       price |      Coef.   Std. Err.      t    P>|t|                     Beta
-------------+----------------------------------------------------------------
         mpg |  -51.30545   86.87821    -0.59   0.557                -.1006368
      weight |   1.486438   1.026837     1.45   0.152                  .391677
displacement |   2.357987   7.239564     0.33   0.746                 .0734196
       _cons |   2304.461   3783.453     0.61   0.544                        .
------------------------------------------------------------------------------

. qui esttab, beta

. mat beta= e(beta)

. *ALL BETA COEFFICIENTS

. mat list beta

beta[1,4]
             mpg        weight  displacement         _cons
y1    -.10063681     .39167704     .07341964            .z

. *EXTRACT SPECIFIC VALUE

. di beta[1,1]
-.10063681

[Mel Shaun]

Thanks Andrew Musau ! How can I put that in my loop? Unfortunately I receive only the result from the very last country in the regression:

matrix beta = J(14,4, .)
matrix colnames beta = x1 x2 x3 x4
matrix rownames beta = CCDummy1 CCDummy2 CCDummy3 CCDummy4 CCDummy5 CCDummy6 CCDummy7 CCDummy8 CCDummy9 CCDummy10 CCDummy11 CCDummy12 CCDummy13 CCDummy14
local irow=0
foreach i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 {
local ++irow
regress y1 x1 x2 x3 x4 if CCDummy== `i', vce(robust) beta
qui esttab, beta
mat beta = e(beta)
}
matrix list beta

[Andrew Musau]

matrix beta = J(14,4, .)

mat beta = e(beta)

If you give the same name to two or more matrices, the last overwrites all others (or more accurately, the previous to last). Therefore, choose different names. Your syntax also could be made more efficient, e.g.,

foreach i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 {

can be reduced to

forvalues i= 1/14

Since I do not have any data, I cannot test the following

matrix beta = J(14,4, .)

forvalues i=1/4{
local cnames  "`cnames' x`i'"
}

forvalues i=1/14{
local rnames  "`rnames' CCDummy`i'"
}

matrix colnames beta= `cnames'
matrix rownames beta= `rnames'

local irow= 1
forvalues i= 1/14{
regress y1 x1 x2 x3 x4 if CCDummy== `i', vce(robust) beta
qui esttab, beta
mat b = e(beta)
forvalues j=1/4{
matrix beta[`i',`j']= b[1,`j']
}
local ++irow
}

[Mel Shaun]

Hey! sorry for the late feedback. It worked nicely! Thank you Andrew Musau :-)

I thought maybe I can also apply this to another case. I like to generate a table for the missing values for my variables for each country:

matrix missingvalues = J(14,8, .)

forvalues i=1/8{
local columnnames  "`columnnames' x`i'"
}

forvalues i=1/14{
local rnames  "`rnames' CCDummy`i'"
}

matrix colnames missingvalues= `columnnames'
matrix rownames missingvalues= `rnames'

local irow= 1
forvalues i= 1/14{
mdesc Growth Productivity ROA Leverage Tangibility Liquidity Firmsize Numberofemployees_n1 ab(20)
esttab
mat missing = r(miss)
forvalues j=1/8{
matrix missingvalues[`i',`j']= r(miss)[1,`j']
}
local ++irow
}

This mdesc gives the table of missing values and it stores the value of missing values in r(miss). I thought that would be similar to the e(beta). But I don't know why it doesn't work this way (the matrix is empty).

[Nick Cox]

Please explain the provenance of mdesc (FAQ Advice #12).

 
 mat missing = J(1, 1, r(miss))

should work, but r(miss) is not, I guess, a matrix. If so for that reason alone you can't subscript it.

[Mel Shaun]

Hi! it still doesn't work. It says: 'factor variables and time-series operators not allowed' Actually the variables I want to describe are not factor variables and not time-series. But my CCDummy, which indicates the country is factor then I think. So mdesc displays the number and proportion of missing values for each variable in varlist and r(miss) is the scalar. It contains the number of missing ones and r(total) has all the variables. Hence, I thought I can have like a matrix which has in the very first column my variable names, second column the number of missings and the third column the total number.

matrix missingvalues = J(14,8, .)
forvalues i=1/8 {
local columnnames "`columnnames' x`i'"
}
forvalues i=1/14{
local rnames "`rnames' CCDummy`i'"
}
matrix colnames missingvalues= `columnnames'
matrix rownames missingvalues= `rnames'
local irow= 1
forvalues i= 1/14{
mdesc Growth Productivity ROA Leverage Tangibility Liquidity Firmsize Numberofemployees_n1 ab(20)
esttab
mat missing = J(1, 1, r(miss))
forvalues j=1/8{
matrix missingvalues[`i',`j']= r(miss)[1,`j']
}
local ++irow
}

[Nick Cox]

You give no data example and no precise details of where the error arises, so it's hard to check anything much.

But I already hinted in #10 that it is unlikely that r(miss) can be subscripted.

Concretely, I can confirm that by looking at the code of mdesc (SSC, as you are asked to explain): it's a scalar. and therefore not even a matrix.

I confess that I didn't earlier look at your code very fully. But there seem to be several problems in roughly the second half.

Let's focus on that and tidy it up before discussing it. Paying attention to spacing and indentation makes your code easier for others to read (really, really important if you want their help in debugging, as you do) and you should benefit too.

local irow = 1
forvalues i = 1/14 {    
    mdesc Growth Productivity ROA Leverage Tangibility Liquidity Firmsize Numberofemployees_n1 ab(20)    
    esttab    
    mat missing = r(miss)    
    forvalues j = 1/8 {        
          matrix missingvalues[`i',`j']= r(miss)[1,`j']    
    }
    local ++irow
}

Problem 1 (Trivial, but distracting). You never use irow, so it can go.

forvalues i = 1/14 {    
    mdesc Growth Productivity ROA Leverage Tangibility Liquidity Firmsize Numberofemployees_n1 ab(20)    
    esttab    
    mat missing = r(miss)    
    forvalues j = 1/8 {        
        matrix missingvalues[`i',`j']= r(miss)[1,`j']    
    }
}

Problem 2 (Trivial, but distracting). You never use the matrix missing, so it can go.

forvalues i = 1/14 {    
      mdesc Growth Productivity ROA Leverage Tangibility Liquidity Firmsize Numberofemployees_n1 ab(20)    
      esttab    
      forvalues j = 1/8 {        
            matrix missingvalues[`i',`j']= r(miss)[1,`j']    
      }
}

Problem 3 (A bug). r(miss) can't be subscripted.

forvalues i = 1/14 {    
       mdesc Growth Productivity ROA Leverage Tangibility Liquidity Firmsize Numberofemployees_n1 ab(20)    
       esttab    
       forvalues j = 1/8 {        
             matrix missingvalues[`i',`j']= r(miss)    
       }
}

Problems 4, 5, 6, 7, 8 and perhaps more.

4. I wouldn't trust that r(miss) is still accessible after esttab. I would use it while it is still accessible (fixed below).

5. I don't ever use mdesc but the syntax looks wrong. ab(20) looks like an option. You need a comma before the options. Not fixed.

6. On the face of it you're doing exactly the same thing 14 times. Something wrong there, or the code will be fixed later.

7. On the face of it you are putting the same result in all columns of each row of a matrix. Same comment.

8. esttab (Stata Journal, as you are asked to explain) is an outstandingly good program that I never use. I don't know what it's doing here.

forvalues i = 1/14 {    
    mdesc Growth Productivity ROA Leverage Tangibility Liquidity Firmsize Numberofemployees_n1 ab(20)    
    forvalues j = 1/8 {        
        matrix missingvalues[`i',`j']= r(miss)    
    }      
    esttab
}

All that said, here's something more positive. missings from the Stata Journal will tabulate number and percent of missing values.

. webuse nlswork
(National Longitudinal Survey.  Young Women 14-26 years of age in 1968)

. missings report

Checking missings in all variables:
15082 observations with missing values

----------------------
          | # missing
----------+-----------
      age |        24
      msp |        16
  nev_mar |        16
    grade |         2
 not_smsa |         8
   c_city |         8
    south |         8
 ind_code |       341
 occ_code |       121
    union |      9296
   wks_ue |      5704
   tenure |       433
    hours |        67
 wks_work |       703
----------------------

. missings report, percent

Checking missings in all variables:
15082 observations with missing values

---------------------------------
          | # missing  % missing
----------+----------------------
      age |        24       0.08
      msp |        16       0.06
  nev_mar |        16       0.06
    grade |         2       0.01
 not_smsa |         8       0.03
   c_city |         8       0.03
    south |         8       0.03
 ind_code |       341       1.20
 occ_code |       121       0.42
    union |      9296      32.58
   wks_ue |      5704      19.99
   tenure |       433       1.52
    hours |        67       0.23
 wks_work |       703       2.46
---------------------------------

. missings report, percent sort

Checking missings in all variables:
15082 observations with missing values

---------------------------------
          | # missing  % missing
----------+----------------------
    union |      9296      32.58
   wks_ue |      5704      19.99
 wks_work |       703       2.46
   tenure |       433       1.52
 ind_code |       341       1.20
 occ_code |       121       0.42
    hours |        67       0.23
      age |        24       0.08
  nev_mar |        16       0.06
      msp |        16       0.06
   c_city |         8       0.03
    south |         8       0.03
 not_smsa |         8       0.03
    grade |         2       0.01
---------------------------------

SJ-17-3 dm0085_1  . . . . . . . . . . . . . . . . Software update for missings
        (help missings if installed)  . . . . . . . . . . . . . . .  N. J. Cox
        Q3/17   SJ 17(3):779
        identify() and sort options have been added


SJ-15-4 dm0085  Speaking Stata: A set of utilities for managing missing values
        (help missings if installed)  . . . . . . . . . . . . . . .  N. J. Cox
        Q4/15   SJ 15(4):1174--1185
        provides command, missings, as a replacement for, and extension
        of, previous commands nmissing and dropmiss

[Nick Cox]

Cosmetic note: I would usually indent so that a tab is a fixed number of spaces. (I used to be an 8 space person, but now am more usually a 4 space person.)

Sometimes the forum software here doesn't honour the spacing in copying and pasting from other software, or from itself to itself, or otherwise gets in a mess, or at least so it seems. Hence the code fragments above are not exactly consistent at 4 space indents, as I would prefer.