stata

Stata putexcel: add numbers with stars to a table


I want to create a table for Excel, based on numbers from Stata. A cell might have a number and stars (indicating statistical significance), such as "0.15***".

Minimal example attempts in Stata (potentially change path from c:\temp if you want to use it):

clear all
sysuse auto.dta    
putexcel set "c:\temp\test.xlsx", sheet(Sheet1) replace //create file

//just create some numbers
quietly sum price if foreign==0
local meandomestic=`r(mean)'/1000
quietly sum price if foreign==1
local meanforeign=`r(mean)'/1000
local diffrounded=round(`meandomestic'-`meanforeign',0.01) 

//manually add stars in a string   
local output "`diffrounded'***"
putexcel A1 = "`output'" //yields wrong number formatting

//second attempt
putexcel B1 = `diffrounded', nformat("0.00***") //causes excel error
putexcel C1 = `diffrounded', nformat("0.00+++") //doesn't cause excel error

I almost got there by manually putting "***" at the end with a string, except that the number formatting isn't right, as a 0 in front of the decimal is missing. It displays

-.31***

in Excel (cell A1). Is there a way to fix this formatting? That would be the easiest solution.

I then tried another way (cell B1 and C1), which works in Stata but creates a faulty Excel file with the following error: enter image description here After saying yes, the stars do not appear in Excel (cell B1), but cell C1 is exactly right, which means for some reason the stars mess up the created Excel file but the command works with other characters. Any way to fix this, though it appears to be more of an Excel issue than a Stata issue?

Finally, I tried some string function magic (use along with above code):

//third attempt using string functions
if substr("`diffrounded'",1,2)=="-." {
    local temp `substr("`diffrounded'",2,.)'
    local diffrounded "-0`temp'***"
}    
putexcel D1 = "`diffrounded'"

The cell in Excel displays "-0***", so clearly the original number isn't put back in.

Any fix to any attempt would be very much appreciated!

PS: Naturally, stars are a consequence of a statistical test. Later in the actual application; these will be added to the numbers depending on a p-value from a statistical test - I omitted this here to keep the example shorter.


Solution

  • I figured it out. The line

    putexcel B1 = `diffrounded', nformat("0.00***") //causes excel error
    

    should be

    putexcel B1 = `diffrounded', nformat(0.00"***") //works
    

    The reason why the first version causes an error in Excel is that this is not actually put in as a string but as a number with "number formatting", and using * there is illegal, but using "*" is OK.