get coefficient of a Multiple Regression

[Alex Potter]

Hello all,

May I ask a question about OLS regression (Y=alpha+a1*X1+a2*X2+a3*X3)?

In Panel A, I have Y, X1, X2 and X3 for each Firm_ID on each Date, I expect to get alpha, a1, a2 and a3 for each Firm_ID in each year (i.e., Panel B).

Panel A							Panel B
Firm_ID	Year	Date	Y	X1	X2	X3	alpha	a1	a2	a3
1	1994	02/17/1994	371	-445	2138	-196	alpha 1	Aaa1	Aaa2	Aaa3
1	1994	02/18/1994	3248	-454	3457	2389	alpha 1	Aaa1	Aaa2	Aaa3
1	1994	02/22/1994	1678	5992	-62	1747	alpha 1	Aaa1	Aaa2	Aaa3
1	1995	02/17/1995	-11	-12	1848	-263	alpha 2	Bbb1	Bbb2	Bbb3
1	1995	02/18/1995	-3803	-1253	3372	1806	alpha 2	Bbb1	Bbb2	Bbb3
1	1995	02/22/1995	4456	3632	-21	291	alpha 2	Bbb1	Bbb2	Bbb3
2	1994	02/17/1994	-8995	3925	8434	-326	alpha 3	Ccc1	Ccc2	Ccc3
2	1994	02/18/1994	229	-506	-235	2486	alpha 3	Ccc1	Ccc2	Ccc3
2	1994	02/22/1994	208	-184	-594	-204	alpha 3	Ccc1	Ccc2	Ccc3
2	1995	02/17/1995	-12	-223	4152	-32	alpha 4	Ddd1	Ddd2	Ddd3
2	1995	02/18/1995	1578	4563	369	-88	alpha 4	Ddd1	Ddd2	Ddd3

Could you please give me some advice about this?

Many thanks in advance.

[Bruce Weaver]

I wonder if -statsby- will get you part way to where you want to be. Here is a modified version of the first example in the help for -statsby-:

clear *
sysuse auto
statsby, by(foreign rep78): regress mpg gear turn

After running that code, the active dataset looks like this:

. list, clean

        foreign   rep78   _b_gear~o     _b_turn    _b_cons  
  1.   Domestic       1           0          -3        144  
  2.   Domestic       2    3.582871   -1.123752   58.24326  
  3.   Domestic       3    4.330716   -.6136569   32.50746  
  4.   Domestic       4    .2404532   -1.006809   60.08332  
  5.   Domestic       5           0           2        -40  
  6.    Foreign       3    6.060603   -1.439394   52.39395  
  7.    Foreign       4   -1.852228   -1.420777    81.3973  
  8.    Foreign       5    20.77394    -1.31126   4.078776

For your sample data:

statsby, by(FirmID Year): regress Y X1 X2 X3

You could then do a 1 to many merge with the original dataset, and rename variables as desired.

[Carlo Lazzaro]

Alex:
despite I'm not clear with what you're after, I do hope you find what follows helpful:

. use "https://www.stata-press.com/data/r17/nlswork.dta"
(National Longitudinal Survey of Young Women, 14-24 years old in 1968)

. xtreg ln_wage ttl_exp i.year, vce(cluster idcode)

Random-effects GLS regression                   Number of obs     =     28,534
Group variable: idcode                          Number of groups  =      4,711

R-squared:                                      Obs per group:
     Within  = 0.1435                                         min =          1
     Between = 0.2632                                         avg =        6.1
     Overall = 0.1893                                         max =         15

                                                Wald chi2(15)     =    2152.37
corr(u_i, X) = 0 (assumed)                      Prob > chi2       =     0.0000

                             (Std. err. adjusted for 4,711 clusters in idcode)
------------------------------------------------------------------------------
             |               Robust
     ln_wage | Coefficient  std. err.      z    P>|z|     [95% conf. interval]
-------------+----------------------------------------------------------------
     ttl_exp |   .0478522   .0015357    31.16   0.000     .0448423     .050862
             |
        year |
         69  |   .0644839   .0101172     6.37   0.000     .0446545    .0843133
         70  |   .0147421   .0102654     1.44   0.151    -.0053777    .0348618
         71  |   .0382671   .0108514     3.53   0.000     .0169987    .0595355
         72  |   .0279605   .0117506     2.38   0.017     .0049298    .0509913
         73  |   .0112526   .0119755     0.94   0.347    -.0122189    .0347242
         75  |  -.0085966   .0121499    -0.71   0.479    -.0324099    .0152166
         77  |   .0174697    .012911     1.35   0.176    -.0078355    .0427748
         78  |   .0229001   .0138115     1.66   0.097      -.00417    .0499701
         80  |  -.0333595   .0148297    -2.25   0.024    -.0624252   -.0042939
         82  |  -.0812514   .0160793    -5.05   0.000    -.1127662   -.0497366
         83  |  -.0914416   .0172207    -5.31   0.000    -.1251935   -.0576897
         85  |  -.1081525   .0184904    -5.85   0.000     -.144393    -.071912
         87  |  -.1621729   .0206003    -7.87   0.000    -.2025486   -.1217971
         88  |  -.1551372   .0229139    -6.77   0.000    -.2000475   -.1102269
             |
       _cons |   1.421198   .0098903   143.70   0.000     1.401813    1.440583
-------------+----------------------------------------------------------------
     sigma_u |  .32392406
     sigma_e |  .29646105
         rho |  .54418111   (fraction of variance due to u_i)
------------------------------------------------------------------------------

. mat list e(b)

e(b)[1,17]
                       68b.         69.         70.         71.         72.         73.         75.         77.         78.         80.
       ttl_exp        year        year        year        year        year        year        year        year        year        year
y1   .04785218           0    .0644839   .01474205   .03826713   .02796053   .01125264  -.00859665   .01746966   .02290007  -.03335953

            82.         83.         85.         87.         88.            
          year        year        year        year        year       _cons
y1  -.08125142  -.09144159  -.10815254  -.16217289  -.15513722   1.4211979

. predict fitted, xb

. list idcode year ttl_exp fitted in 1/20

     +-------------------------------------+
     | idcode   year    ttl_exp     fitted |
     |-------------------------------------|
  1. |      1     70   1.083333    1.48778 |
  2. |      1     71   1.275641   1.520507 |
  3. |      1     72    2.25641   1.557133 |
  4. |      1     73   2.314102   1.543185 |
  5. |      1     75   2.775641   1.545422 |
     |-------------------------------------|
  6. |      1     77   3.775641    1.61934 |
  7. |      1     78   3.852564   1.628452 |
  8. |      1     80   5.294872    1.64121 |
  9. |      1     83   5.294872   1.583127 |
 10. |      1     85   7.160256   1.655679 |
     |-------------------------------------|
 11. |      1     87    8.98718   1.689081 |
 12. |      1     88   10.33333   1.760533 |
 13. |      2     71   .7115384   1.493514 |
 14. |      2     72   1.134615   1.503452 |
 15. |      2     73   1.461538   1.502388 |
     |-------------------------------------|
 16. |      2     75   2.211539   1.518428 |
 17. |      2     77   3.211539   1.592347 |
 18. |      2     78   4.211538   1.645629 |
 19. |      2     80   6.096154   1.679553 |
 20. |      2     82   7.666667   1.706813 |
     +-------------------------------------+

. di 1.421198 + .01474205 + (1.083333*.04785218)
1.4877799

.

1)

mat list e(b)

gives you back all the coefficients calculated via your regression;

2)

predict fitted, xb

gives you back the predicted values for each observation. Despite this wonderful post estimation command saves hours of your time that should be otherwise devoted to the very same calculation made by hand, I personally find useful to calulate the predicted values by hand (some of them, at least) just to be sure that Stata and Carlo are on the same page (that Stata reached in a fraction of second and Carlo in 30 minutes, when he is on a roll ).

PS; crossed in the cyberspace with Bruce's helpful reply, that is problay nearer to what Alex would like to do with his data.

[Nick Cox]

The example in #1 is a little puzzling as you won't be able to fit a multiple regression with 3 predictors usefully, if at all, to 2 or 3 observations. Assuming it's purely fictitious, note a key difference: statsby produces a condensed dataset, whereas you want coefficients to be new variables alongside the existing dataset.

Here is a silly example using rangestat from SSC. I fit separate regressions for each company in the Grunfeld dataset.

webuse grunfeld, clear

rangestat (reg) mvalue invest kstock , int(year . .) by(company)

l company reg_nobs-b_cons if year == 1954

     +----------------------------------------------------------------------------------+
     | company   reg_nobs      reg_r2   reg_adj_r2    b_invest     b_kstock      b_cons |
     |----------------------------------------------------------------------------------|
 20. |       1         20    .6396019    .59720212   4.6641834    -1.456692   2442.4983 |
 40. |       2         20   .26078281    .17381608   1.4082486    -.3478283   1496.3331 |
 60. |       3         20   .15807154    .05902114   5.5038334   -.66817807    1645.716 |
 80. |       4         20   .52428574    .46831935   6.0623386   -1.6756787   374.26785 |
100. |       5         20   .83490452    .81548152   1.9882126    .22060632   1.2100534 |
     |----------------------------------------------------------------------------------|
120. |       6         20   .96327951    .95895946   3.8892515    1.1803837    81.26137 |
140. |       7         20   .09523182   -.01121149   1.0822395   -.12839337   138.71712 |
160. |       8         20   .73010272     .6983501   7.5653928    .88984862   270.21232 |
180. |       9         20   .50906919    .45131262   2.9820944    .14216511   166.38206 |
200. |      10         20    .0241698   -.09063376    .3640699   -.59845633   73.353755 |
     +----------------------------------------------------------------------------------+

.
Notes:

1. Standard errors of each coefficient are also produced.

2. Had there been many observations in each year as well as for each company, the call might have been

rangestat (reg) mvalue invest kstock , int(year 0 0) by(company)