putexcel within loop

[rouault eleonor]

Good morning everyone,

This question is related to an existing topic (https://www.statalist.org/forums/for...xport-to-excel), but I did not find any reliable solution to solve my problem.
I want to extract estimated betas and p-value in separate regressions and put them into an excel file.

putexcel set "PATH\results", replace

local row = 2

foreach c of global VAR {
reg y i.`VAR' $county i.year, cluster(county)
putexcel A`row'=("`c'")
scalar beta = r(table)[1,1]
scalar pval = r(table)[4,1]
putexcel B`row'= beta
putexcel D`row'= pval
local ++row
}

I obtain only "0" in column B. Column A is correct. I think the mistake comes from the r(table). How can I correct my code ?

[Andrew Musau]

It should be a formatting issue within MS Excel. You can ask MS Excel to display more decimals.


$$\text{Format Cells } \rightarrow \text{ Number } \rightarrow\; \cdots$$

[rouault eleonor]

It seems that the problem does not come from Excel. I obtain 0.00000.

[Andrew Musau]

I did not pay attention to your loop. You have a factor variable, so the base coefficient is always zero which is what you are outputting. In any case, a categorical variable with many levels will have multiple coefficients. If the variable is binary, the displayed coefficient is stored in r(table)[1,2].

sysuse auto, clear
reg mpg i.rep78
di r(table)[1,1]
mat l r(table)

Res.:

. reg mpg i.rep78

      Source |       SS           df       MS      Number of obs   =        69
-------------+----------------------------------   F(4, 64)        =      4.91
       Model |  549.415777         4  137.353944   Prob > F        =    0.0016
    Residual |  1790.78712        64  27.9810488   R-squared       =    0.2348
-------------+----------------------------------   Adj R-squared   =    0.1869
       Total |   2340.2029        68  34.4147485   Root MSE        =    5.2897

------------------------------------------------------------------------------
         mpg |      Coef.   Std. Err.      t    P>|t|     [95% Conf. Interval]
-------------+----------------------------------------------------------------
       rep78 |
          2  |     -1.875   4.181884    -0.45   0.655    -10.22927    6.479274
          3  |  -1.566667   3.863059    -0.41   0.686    -9.284014    6.150681
          4  |   .6666667   3.942718     0.17   0.866    -7.209818    8.543152
          5  |   6.363636   4.066234     1.56   0.123    -1.759599    14.48687
             |
       _cons |         21   3.740391     5.61   0.000     13.52771    28.47229
------------------------------------------------------------------------------

. di r(table)[1,1]
0

. mat l r(table)

r(table)[9,6]
                1b.          2.          3.          4.          5.            
             rep78       rep78       rep78       rep78       rep78       _cons
     b           0      -1.875  -1.5666667   .66666667   6.3636364          21
    se           .   4.1818842   3.8630592   3.9427182   4.0662336   3.7403909
     t           .  -.44836249  -.40555078   .16908808   1.5649953   5.6143864
pvalue           .   .65540591   .68642531   .86626065    .1225156   4.570e-07
    ll           .  -10.229274  -9.2840145  -7.2098185  -1.7595992    13.52771
    ul           .   6.4792741   6.1506812   8.5431518   14.486872    28.47229
    df          64          64          64          64          64          64
  crit   1.9977297   1.9977297   1.9977297   1.9977297   1.9977297   1.9977297
 eform           0           0           0           0           0           0

[rouault eleonor]

Thank you very much Andrew, I make some corrections and it works perfectly!