Export matrix to excel. How to asign rows on a loop

[Nicolas Morales]

Hello,

I'm estimating a model where I have 74,000 equations, and I want to export the estimators to an excel matrix.
My loop looks like this:

putexcel set elasticities.xls, replace

foreach z of {

foreach v of num 1/273 {

display `v'
gen ln_proportion`v' = ln(proportion`v')

foreach k of num 1/273 {

display `k'
reg ln_proportion`v' p_q`v' price`v' pr`k' income
putexcel `z'`k' = e(b)

}
}
}

The problem is the outside of the loop, cause I don't know how to assign the corresponding letters to the rows on excel (A,B,C....AA,AB,AC)

I was thinking of using the tokenize command to generate the whole ABCDEFGH.... code and break it into small parts but I haven't used it before and can't figure out how to do it.

[William Lisowski]

First note that in Excel, the rows are numbered and the columns are named A B C ... AA AB AC.

It seems to me you want to create a 273x273 matrix in your Excel workbook, but the fact is that for your regression e(b) will be a row vector with one column for each of your coefficients.

. putexcel set gnxl.xls, replace
Note: file will be replaced when the first putexcel command is issued

. sysuse auto, clear
(1978 Automobile Data)

. regress weight length displacement

      Source |       SS           df       MS      Number of obs   =        74
-------------+----------------------------------   F(2, 71)        =    480.99
       Model |  41063449.8         2  20531724.9   Prob > F        =    0.0000
    Residual |  3030728.55        71  42686.3176   R-squared       =    0.9313
-------------+----------------------------------   Adj R-squared   =    0.9293
       Total |  44094178.4        73  604029.841   Root MSE        =    206.61

------------------------------------------------------------------------------
      weight |      Coef.   Std. Err.      t    P>|t|     [95% Conf. Interval]
-------------+----------------------------------------------------------------
      length |   22.91788   1.974431    11.61   0.000     18.98097    26.85478
displacement |   2.932772   .4787094     6.13   0.000     1.978252    3.887291
       _cons |  -1866.181   297.7349    -6.27   0.000    -2459.847   -1272.514
------------------------------------------------------------------------------

. matrix list e(b)

e(b)[1,3]
          length  displacement         _cons
y1     22.917876     2.9327718    -1866.1807

. putexcel c2=matrix(e(b))
file gnxl.xls saved

. putexcel close

And so when we open this in Excel we see that this regression requires three cells to store the estimates.



With that said, the approach I would take would be to build your matrix - whatever it is - in Stata using the matrix (not Mata!) commands documented in the output of help matrix and then use putexcel to output the entire matrix to a location specified by its upper left corner.