Convert table to matrix and export as excel file

[Marcela Vieira]

Hi everyone,

I am trying to convert this table to a matrix and export it as an excel file. Sorry, I am new to STATA. Any help is much appreciated.

[Andrew Musau]

You will lose the labels if exporting a matrix. Consider:

ssc install estout, replace

sysuse auto, clear
mean mpg if !inlist(rep78, 1, 2), over(rep78 foreign)
esttab using myfile.csv, cells("b se ci_l ci_u") nonumb replace

Res.:

. esttab, cells("b se ci_l ci_u") nonumb replace

----------------------------------------------------------------
                     Mean                                      
                        b           se        min95        max95
----------------------------------------------------------------
[email protected]~n           19     .7862783     17.42609     20.57391
[email protected]~n     23.33333     1.452966     20.42491     26.24176
[email protected]~n     18.44444     1.528535     15.38475     21.50414
[email protected]~n     24.88889     .9043789     23.07858      26.6992
[email protected]~n           32            2     27.99657     36.00343
[email protected]~n     26.33333     3.122499     20.08297     32.58369
----------------------------------------------------------------
N                      59                                      
----------------------------------------------------------------

[Marcela Vieira]

Hi Andrew,

Thank you so much for your response. As I explore my dataset I realise that what I am doing is probably not the best approach, instead, I would like to generate a new dataset containing lgacodes species_code year1_lga and mean_abundance, then export it as a CSV file.

My dataset contains:

- mcruns (50 Monte Carlo simulations)
- lgacode (50001 to 50136)
- species_code (1 to 5)
- abund_scaled_int (species abundance)
- year1_lga (1972 to 1981)
[ATTACH=CONFIG]n1625071[/ATTACH]

For each lgacode, species_code and year I would like to have the mean abundance instead of 50 (simulations) - the number of observation would be reduced considerably!

I tried:

mean abund_scaled_int, over(lgacodes species_code year1_lga)

but got an error message:
invalid over() option;
maxvar too small

I believe a loop would solve my problem but I am not familiar with loops. Some sort of

foreach abund_scaled_int in lgacodes species_code year1_lga {
egen mean = mean(`abund_scaled_int')
}

Would you have any tips on how to have a dataset without the 50 mcruns, instead, having only lgacode, species_code year1_lga and the mean_abundance ?

Thanks a lot!

[Andrew Musau]

Hi Marcela Vieira, I am not completely following this. A guess is that you want:

collapse abund_scaled_int, by(gacodes species_code year1_lga)
export excel using myfile, replace

If not, show a sample of your data using dataex and explain how you want the output to look like (see FAQ#12 for details on dataex).