Problem with posting results to Excel file

[Fathima Salih]

Hi,

I want the code below to help produce an excel output with item list in the rows and corresponding means by quintile, mix, max and number. The output unfortunately includes the entire original dataset instead of aneat table. Can someone please advise? Thank you in advance!

xtile q2004=wealth_index2004 if wealth_index2004!=., nq(5)
tempfile results 
save `results', emptyok

local list2 red blue green black

foreach var of local list2 {
    
    * means by quintile
    foreach q in 1 2 3 4 5 {
        quietly sum `var' if q2004== `q', meanonly
        local mean`q'=r(mean)
    }
    
    *overall stats
    quietly sum `var', meanonly
    local mean_all = r(mean)
    local minval = r(min)
    local maxval = r(max)
    local Nobs=r(N)
    
    *create new dataset with sumary results
    preserve
    clear
    set obs 1
    gen model = "`var'"
    gen Q1 = `mean1'
    gen Q2 = `mean2'
    gen Q3 = `mean3'
    gen Q4 = `mean4'
    gen Q5 = `mean5'
    gen mean = `mean_all'
    gen min = `minval'
    gen max = `maxval'
    gen N = `Nobs'
    
    tempfile one
    save `one'
    
    * Append to growing results dataset
    use `results', clear
    append using `one'
    save `results', replace
    restore
}

use `results', clear
export excel using "summary_table.xlsx", firstrow (variables) replace

[Clyde Schechter]

I think your problem arises from the very beginning of the code. You start with:

xtile q2004=wealth_index2004 if wealth_index2004!=., nq(5)
tempfile results
save `results', emptyok

Now, the fact that you included the -emptyok- option on your -save- command suggests that you expected to save an empty file. But the fact that the -xtile- command could run without halting execution and an error message tells me that in fact you had data in active memory. So your -save- command saved that data into file `results'. The rest of the code then appends the various quintile summary statistics to that.

So you need to start with an empty data set. Using the -emptyok- option does not do that. It tells Stata that it is allowable to do the save even if there is no data in memory to save, but it does not force Stata to save an empty data set when there is data present. So I would handle this by starting the code with

clear
tempfile results
save `results', emptyok

Then, -use- your data set, run the -xtile- command, and then the rest of the code.

If this does not solve the problem, when you reply please post example data using the -dataex- command.

[Fathima Salih]

Thanks Clyde! It worked!