Help needed in outreg2 and putexcel

[Wee Yang Ng]

Hi,

I am using Stata 17 and need some help in producing an excel document that contains both regression and correlation analysis output, Below is an example of my dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input double actual_price byte(korea uk germany) double engine_sizeL byte(turbo_premium full_manual Four_WD full_airbags tire_pressure_monitor parking_aid transmission_warranty)
     35 0 0 1   2 0 0 1 0 1 1  4
   57.1 0 0 0 5.3 0 0 0 0 1 1  5
 41.995 0 0 0   2 0 0 1 0 1 1  4
   34.4 0 0 0   2 0 0 1 0 1 0  6
 26.295 0 0 0 2.4 0 0 0 1 1 0  5
  62.09 0 0 0 5.6 0 0 1 0 1 1  5
   21.3 1 0 0   2 0 0 0 0 1 0 10
 39.995 0 0 0 3.6 0 0 0 0 1 1  6
   24.8 0 0 0 2.5 0 0 0 0 1 0  5
 86.995 0 0 0 6.2 0 0 0 1 1 1  6
   53.1 0 1 0   2 0 0 1 0 1 0  5
   45.2 0 0 1   2 0 0 1 0 1 0  4
   56.4 0 0 1   3 0 0 1 0 1 1  4
   60.1 0 1 0   2 0 0 1 0 1 1  4
100.295 0 0 0 6.2 0 0 1 0 1 1  6
 27.395 0 0 0 3.6 0 0 0 0 1 0  5
   25.2 1 0 0   2 0 0 1 0 1 0 10
   52.1 0 0 0 5.3 0 0 1 0 1 1  5
  18.99 0 0 0 2.5 0 1 0 0 1 0  5
 55.495 0 0 0 6.2 0 1 0 0 0 0  5
 23.295 0 0 0 1.5 0 0 0 0 1 0 10
   46.6 0 0 1   2 0 0 1 0 1 0  4
  24.25 0 0 0 2.4 0 0 0 0 1 0  5
  28.11 0 0 0 3.5 0 0 0 0 1 0  5
   33.4 0 0 1   2 0 0 0 0 1 0  4
   42.6 0 0 0   2 0 0 0 0 1 1  5
 27.445 0 0 0 2.4 0 0 1 0 1 0  5
 38.495 0 0 0 3.5 0 0 0 0 1 1  5
 33.995 0 0 0 5.7 0 1 0 0 1 0  5
 59.775 0 1 0   3 0 0 1 0 1 1  5
  31.95 0 0 0 1.5 0 0 1 0 1 0  5
 67.135 0 0 0 3.5 1 0 0 0 1 0  5
 65.495 0 0 0 6.2 0 1 0 0 0 1  5
  76.35 1 0 0   5 0 0 1 1 1 1 10
   41.4 0 0 0   2 0 0 1 0 1 0  6
  54.65 0 0 0 3.5 0 0 0 0 1 1  6
  37.92 0 0 1   3 0 0 0 0 1 0  3
 23.685 0 0 0 2.4 0 0 0 0 1 0  4
  24.59 0 0 0   2 0 0 1 0 1 0  5
   32.6 0 0 0 3.6 0 0 0 0 1 0  5
end

Second part of my dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input int(person_1 person_2 person_3)
 50 30 20
 20 57 50
 30 45 20
 25 55 20
 25 52 20
 50 38 15
 25 17 20
 75 52 40
 40 33 40
 50 75 40
 25 32 19
 75 32 50
 75 65 25
 75 40 20
 75 85 60
 15 37 15
100 70 30
 35 42 15
 25 35 15
 45 80 15
 75 58 20
 20 34 15
 15 34 15
 25 35 15
 45 60 35
 19 28 15
 25 30 20
 65 30 15
 45 32 15
 75 90 50
 15 43 10
100 60 60
 35 45 15
 25 48 50
 75 60 15
 55 26 20
 50 60 40
 40 29 20
 10 23 15
 15 32 15
end

So these are the codes I used so far:

foreach y of varlist person_* {
    reg `y' korea uk germany engine_sizeL turbo_premium full_manual Four_WD full_airbags tire_pressure_monitor parking_aid transmission_warranty  if  auto_id<41, robust
    outreg2 using round2_output, excel
    capture noisily: reg `y' korea uk germany engine_sizeL turbo_premium full_manual Four_WD full_airbags tire_pressure_monitor parking_aid transmission_warranty  if  auto_id> 40, robust
    capture noisily: outreg2 using round2_output, excel
}

putexcel set round2_output.xls, sheet(Round2 Correlations) modify
foreach p of varlist person_* {
    capture noisily: corr `p' actual_price if auto_id > 40
    local i : subinstr local p "person_" "" // change person_ into empty string, leaving only the numbers
    capture noisily: putexcel P`i' = `r(rho)'
}
putexcel save
putexcel clear

So my intention is to produce an output file called "round2_output" with 2 different sheets, one for regression, another for correlation analysis. Unfortunately when I run these codes, I get two separate files of the same name.

What do I have to do in my codes to make all results to be in the same excel file?

Any help in this area would be appreciated. Thanks!

[Andrew Musau]

outreg2 is from SSC, as you are asked to explain (FAQ Advice #12). The program is quite old and has not been updated for years. My guess is that its Excel format differs from those that are supported by putexcel. Otherwise, it is impossible to save two documents with the exact name and exact format in the same folder.

[Wee Yang Ng]

Hi,

in this case, does putexcel produce output similar to outreg2? I am trying to use putexcel to export my regression results table to an excel file but not sure about the codes to make it similar to outreg2.

Thanks.

[Andrew Musau]

I think the newly introduced tables command is what is designed to produce output like esttab or outreg2 (both from SSC). I have not tried it out as I do not have access to Stata 17, but look at some examples by searching the forum. You could replicate the output with putexcel, but you need quite a bit of programming knowledge.