Bookmark and Share

Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: st: retaining numeric formatting when using -outsheet-

From   Neil Shephard <[email protected]>
To   [email protected]
Subject   Re: st: retaining numeric formatting when using -outsheet-
Date   Thu, 23 Dec 2010 15:24:45 +0000

On Thu, Dec 23, 2010 at 3:16 PM, Beede, David N <[email protected]> wrote:
> Hi -
> I know this topic has come up before, and the answer was to set the display format within Stata.  But even so, I am having problems with it.  I have done a lot of due diligence to try to find the answer but can't (I also tried using StatTransfer and -xmlsave- without success).  I want to avoid as much format-fussing in Excel as I can, but if I have to I guess I will. But if anyone has a quick fix, I would be grateful to know it.  Thanks.
> Here is a snippet of code:
>                recast double v*
>                format v* %20.0fc
>                format *
>                l v1 v2
>                outsheet v1 v2 using test.csv, replace comma
> Here is an excerpt from the Stata log corresponding to the snippet:
> variable name  display format
>  -----------------------------
>  v1             %20.0fc
>  v2             %20.0fc
>  -----------------------------
>     +----------------------+
>     | v1                v2 |
>     |----------------------|
>  1. | 21    27,649,503,232 |
>  2. |  0                 0 |
>  3. | 48   213,393,129,472 |
>  4. | 18   185,502,416,896 |
>     +----------------------+
> And here is what the Excel file looks like after using -outsheet-
> v1      v2
> 21      27649503232
> 0       0
> 48      2.13393E+11
> 18      1.85502E+11

This sounds more like an Excel issue.

To investigate open the CSV in a text-editor (NOT Excel or Word) and
look at the format of your strings.

My guess is that they will look as desired, which means its Excel that
is choosing to display "213,393,129,472" as "2.13393E+11" and so
forth.  I don't have a version of Excel with which to check this
(since I exclusively run Linux) but there are ways of formatting
columns in Excel (something like right-clicking the column and
selecting "Format cells" or similar) that should allow you to set the
display format as desired before then saving it as an Excel file (i.e.
with extensions .xls so that formatting will be retained).


"Our civilization would be pitifully immature without the intellectual
revolution led by Darwin" - Motoo Kimura, The Neutral Theory of
Molecular Evolution

Email - [email protected]
Website -
Photos -

*   For searches and help try:

© Copyright 1996–2018 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   Site index