Notice: On March 31, it was **announced** that Statalist is moving from an email list to a **forum**. The old list will shut down on April 23, and its replacement, **statalist.org** is already up and running.

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

From |
"Beede, David N" <dbeede@doc.gov> |

To |
"statalist@hsphsun2.harvard.edu" <statalist@hsphsun2.harvard.edu> |

Subject |
RE: st: retaining numeric formatting when using -outsheet- |

Date |
Thu, 23 Dec 2010 11:05:10 -0500 |

Wow, thank you, Klaus - that worked! (It certainly doesn't seem obvious to me that it would.) I changed the code to this: format v* %20,0fc outsheet v1 v2 using test.txt, replace and test.txt looks like this: v1 v2 21 27,649,503,232 0 0 48 213,393,129,472 18 185,502,416,896 And it looks like this when read into Excel: v1 v2 21 27,649,503,232 0 0 48 213,393,129,472 18 185,502,416,896 Thanks to everyone! -----Original Message----- From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Klaus Pforr Sent: Thursday, December 23, 2010 10:55 AM To: statalist@hsphsun2.harvard.edu Subject: Re: st: retaining numeric formatting when using -outsheet- <> Dear David, this seems to be a problem of your decimal point separation choice. One way is to set the decimal point to comma in stata. set dp comma With this you change the thousands separator to point, which should be accepted by excel without any problems. The other possibility is to change the respective setting in excel (extras->options->international). Here you change the decimal point to point and the thousands separator to comma. Don't do both, otherwise you end up with the same problem, just with 21.27649503232 instead of 21,27649503232. best, Klaus Am 23.12.2010 16:43, schrieb Sergiy Radyakin: > Dear David, > > 1) if you plan using CSV format, perhaps it is not a good idea to > format numbers with > commas like so: 213,393,129,472 since Excel will read this as 4 > different values in > one row. Since you report that Excel shows this as one number, I > conclude that the > file outputted from Stata does not contain the comma separators > between the triplets. > > 2) If the file contains commas between triplets but the values are > separated with TABs, > Excel (2007) reads the numbers correctly, recognizes the triplets > separator / number > format etc. So it seems you need to pursuade Stata to output > everything in correct > format (preserve triplets separator in the outsheet output, which it discards). > > 3) unless there is something undocumented about outsheet, there > appears to be two > options: > A) write your own outsheet, which supports this feature; > B) post-process outsheet's output, either before or after importing to Excel. > > Best, Sergiy Radyakin > > > > > On Thu, Dec 23, 2010 at 10:32 AM, Beede, David N<dbeede@doc.gov> wrote: >> Thank you, Neil. I did as you suggested, and the numbers are not in the desired format: >> v1,v2 >> 21,27649503232 >> 0,0 >> 48,213393129472 >> 18,185502416896 >> So I don't know if this means I am doing something wrong in Stata. I agree I could format in Excel, but I am trying (perhaps too hard!) to automate the process as much as possible. >> >> -----Original Message----- >> From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Neil Shephard >> Sent: Thursday, December 23, 2010 10:25 AM >> To: statalist@hsphsun2.harvard.edu >> Subject: Re: st: retaining numeric formatting when using -outsheet- >> >> On Thu, Dec 23, 2010 at 3:16 PM, Beede, David N<dbeede@doc.gov> 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). >> >> Neil >> >> -- >> "Our civilization would be pitifully immature without the intellectual >> revolution led by Darwin" - Motoo Kimura, The Neutral Theory of >> Molecular Evolution >> >> Email - nshephard@gmail.com >> Website - http://kimura-no-ip.org/ >> Photos - http://www.flickr.com/photos/slackline/ >> >> * >> * For searches and help try: >> * http://www.stata.com/help.cgi?search >> * http://www.stata.com/support/statalist/faq >> * http://www.ats.ucla.edu/stat/stata/ >> >> >> * >> * For searches and help try: >> * http://www.stata.com/help.cgi?search >> * http://www.stata.com/support/statalist/faq >> * http://www.ats.ucla.edu/stat/stata/ >> > * > * For searches and help try: > * http://www.stata.com/help.cgi?search > * http://www.stata.com/support/statalist/faq > * http://www.ats.ucla.edu/stat/stata/ -- __________________________________ Klaus Pforr MZES AB - A Universität Mannheim D - 68131 Mannheim Tel: +49-621-181 2801 (nachmittags) fax: +49-621-181 2803 URL: http://www.mzes.uni-mannheim.de Besucheranschrift: A5, Raum A312 __________________________________ * * For searches and help try: * http://www.stata.com/help.cgi?search * http://www.stata.com/support/statalist/faq * http://www.ats.ucla.edu/stat/stata/ * * For searches and help try: * http://www.stata.com/help.cgi?search * http://www.stata.com/support/statalist/faq * http://www.ats.ucla.edu/stat/stata/

**References**:**st: retaining numeric formatting when using -outsheet-***From:*"Beede, David N" <dbeede@doc.gov>

**Re: st: retaining numeric formatting when using -outsheet-***From:*Neil Shephard <nshephard@gmail.com>

**RE: st: retaining numeric formatting when using -outsheet-***From:*"Beede, David N" <dbeede@doc.gov>

**Re: st: retaining numeric formatting when using -outsheet-***From:*Sergiy Radyakin <serjradyakin@gmail.com>

**Re: st: retaining numeric formatting when using -outsheet-***From:*Klaus Pforr <klaus.pforr@mzes.uni-mannheim.de>

- Prev by Date:
**Re: st: Problem with ereturn repost** - Next by Date:
**st: New version of -xrewide- on SSC** - Previous by thread:
**Re: st: retaining numeric formatting when using -outsheet-** - Next by thread:
**RE: st: retaining numeric formatting when using -outsheet-** - Index(es):