Export of results to Excel (or in Stata) after running loops in a simulation

[Anela Kien]

Hello!
I have a problem with exporting/saving simulation results after every run from Stata to Excel or save them in Stata.

I am running a simulation 1000 times and after I code the following:

Start of the simulation:

local sim = 1000
forval i = 1/`sim' {
local seed = 123456 + `i'
set seed `seed'
.
.
.

Then I calculate the results, I would like to save:

egen x_C = xtile(C), nq(10)
egen x_S = xtile(S), nq(10)

forval y = 1/10{
egen C_`y' = mean(C) if x_C == `y'
egen S_`y' = mean(S) if x_C == `y'
}

Now I run this simulation 1000 times and I would like to save the results for every run into excel.
I am interested in the results for C_`y' and S_`y' after every run of the 1000 simulations.
I would like to have the results in Excel or Stata for every Decile, so I can calculate a mean from the 1000 results for these 20 variable (10 each).

I tried, but can not find a solution.
Could someone maybe help me with this problem?


Thanks a lot in advance!

Best regards
Anela Kien

[Clyde Schechter]

This is actually pretty straightforward:

local sim = 1000
set seed 123456
gen strL rngstate = `""'
frame create results int sim_num byte decile float (C S) strL rngstate

forval i = 1/`sim' {
    replace rngstate = `"`c(rngstate)'"'
    .
    .
    .


    egen x_C = xtile(C), nq(10)
    egen x_S = xtile(S), nq(10)

    forval y = 1/10{
        egen C_`y' = mean(C) if x_C == `y'
        egen S_`y' = mean(S) if x_C == `y'
        summ C_`y' if x_C == `y', meanonly
        local cy = r(mean)
        summ S_`y' if x_C == `y'
        local sy = r(mean)
        frame post results (`i') (`y') (`cy') (`sy') (rngstate)
    }
    ... // CLEANUP CODE NEEDED HERE BEFORE ITERATING AGAIN
}

The bold faced lines are additional code that creates a new Stata data set in frame results which contains the results of your simulation. (Actually one observation for each decile in each iteration of the simulation.) After the simulation runs you can -change frame- to results and do what you wish with its contents, as it is an ordinary Stata data set.

The italicized lines are a change to the way you are handling random number generation. It is generally a bad idea to re-set the random number seed in a program. It can actually introduce dependencies into the resulting random number sequence. It is best to set the seed once and let the random number generator go from there. I assume that you did what you did because you want to be able to re-run the simulation from some point other than the beginning if you find interesting or suspicious results somewhere in the middle of the output. This is an excellent practice when doing simulation. Saving the value of `c(rngstate)', which is a very long string, is a better way to do that. If you have to go back and re-run from some point other than the beginning, you can -set rngstate- with the value of the rngstate saved for the corresponding run.

Added: Actually, at least as far as the code is shown, your use of -egen- inside the -forval y- loop is unnecessary and inefficient. Instead, outside that loop you can just do:

by x_C, sort: egen C_`y' = mean(C)
by x_C: egen S_`y' - mean(S)

and remove those -egen-'s from the loop.

[Anela Kien]

I really have to say, a big thanks for your help, Clyde!
I needed a bit of time to implement your suggestion for appropriate coding as I was not familiar with "frame", but now it works out perfectly!
Also the cleaning up the data was very important for running the code, so thank you too for that hint!

Best regards,
Anela