Export Excel and Decimal Places

[Rebecca Ivester]

Hello-

I am exporting data from Stata using export excel; however, the exported numbers are rounded to the integer. I don't see an option for it, but is there a way to specify number of decimal places I'd like to output? Or maybe this is an Excel issue?

Thanks

[Clyde Schechter]

As far as I know, there is no option for this. And, I agree it's a pain. Since it is possible to control this from within Stata using -putexcel-, I don't see why they don't have a -usedisplayformat- option or something like that on -export excel-. But they don't, or if they do, it's not documented and I don't know about it.

But one thing that is helpful: if you are going to be running this same code multiple times, after the first run, go into Excel and set the decimal places you want. Then on subsequent runs of the code, use the -sheet(sheetname, modify)- option and also specify the -keepcellfmt- option. That will preserve the decimal places (and any other cell formatting you specified in Excel) on subsequent runs.

[Isaac Maddow-Zimet]

Another solution to this is to convert the variables you are exporting to string (formatted to the correct number of decimals) before you export. So the code might look something like:

  tostring varname, gen(varnamestring) force format(%9.2f)

This doesn't rely on whatever Excel formatting is present, and also helps with some other unwanted Excel behavior (see the note on how to store dates in Excel in Woo and Broman, Data Organization in Spreadsheets, published in the American Statistician, for an example of this).

[Suzanna Vidmar]

Just checking if anyone's aware whether there's now a direct way to specify the number of decimal places when exporting to Excel via "export excel". In the meantime, here's my workaround:

foreach var of varlist age gestage weight height bmi zweight zheight zbmi {
    tostring `var', gen(`var'_s) force format(%9.2f)
    destring `var'_s, gen(`var'_sn)
    drop `var' `var'_s
    rename `var'_sn `var'
}

export excel "..\Processed Data\anthro_z.xlsx",firstrow(var) keepcellfmt replace

NOTE: I want the final set of variables to be numeric, not string.

[Clyde Schechter]

As far as I know, -export excel- has not changed in regard to this.

A different workaround is possible, using -export delimited-. That has a -datafmt- option which causes the Stata display format for numeric variables to be retained in the exported file. The default file format for -export delimited- is .csv, which is immediately readable by Excel, and you can re-save it as .xlsx. Of course, this has limitations: you can't do multiple sheets within a .csv file.

[Suzanna Vidmar]

Thanks Clyde for your very quick response. I'll check if people would be happy with CSV files.
Regards,
Suzanna

[Nick Cox]

There is a different issue. tostring followed by destring can't be a good way of rounding numeric variables to 2 decimal places. Consider the decimals { 0, 1, ..., 99 } / 100. Of these only { 0, 25, 50, 75 } / 100 can be held exactly in binary; all others are held with binary approximations. Even 0.01 can't be held exactly in binary. This problem is much discussed here under the heading of precision.

Rounding to 2 decimal places for presentation is a really good idea, but that is not the way to do it.

Sorry, but I am no kind of expert on either MS Excel or any other part of MS Office to be able to suggest what you should be doing. But it's a natural general principle to keep as much detail as you can with numbers and round as a cosmetic operation in the last software you use.

As the putative author of tostring and destring, I can say that this dual operation isn't an intended use.