Export excel decimal

[Abdan Syakura]

Dear all,

Could you advise how I can use export excel command with specific number of decimal? I couldn't find the info on the export manual. I want export the data with two decimal only. I tried

nformat(number_d2)

and

dec(2)

, but doesn't work. Thank you.

Best,

Abdan

[Nick Cox]

Despite the "doesn't work" -- which we explicitly advise against in the FAQ as a problem report -- this will be, I guess, a precision issue. Consider all the possible fractions 1/100 2/100 to 99/100. Only 3 of 99 cases allow exact binary approximations, namely 25, 50 and 75/100. Hence what you may well see is a Stata's best attempt at an approximation, translated from decimal to binary and back to decimal.

search precision

for (much) more discussion, starting with William Gould's blog posts. What you could do is export decimals as string equivalents, but I don't advise that. Just take this as indicating that you may need to tweak display format, or whatever it is called locally, in MS Excel, which I don't really know much about. (I'm a Stata person.)

[William Lisowski]

The two things you say you tried - I don't see them documented as options of export excel - so I don't even understand what code you tried or in what way it did not work.

You can use the putexcel command to format cells in an Excel worksheet using the nformat() option, but "number_d2" is not what is document for an Excel numeric format. You perhaps would want nformat(0.00) or something similar.

[Andrés Lahur Talavera Cuya]

how to customize cell format with putexcel?: decimal symbol= ,
number of decimal digits= 1
thousands separation symbol= " " clear all
cd "D:\Curso\sesion8"
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte c_act float id double facpob float(pet res14)
0 20 0 . 1
0 20 0 . 1
3 20 932.4718552954527 1 1
3 20 880.8480025758004 1 1
3 8 793.0467553271009 1 1
1 8 503.9832822063969 1 1
1 20 801.4179752110739 1 1
1 20 490.4903033457613 1 1
1 8 754.0348206213515 1 1
. 8 . . 1
1 8 801.1122168545756 1 1
2 20 345.8627947845902 1 1
. 20 . . 1
1 8 350.89240955191514 1 1
1 8 798.6087408547241 1 1
3 20 170.02498717942945 1 1
1 20 1308.5380868109737 1 1
1 8 749.5342081880037 1 1
3 8 1591.8958755441195 1 1
. 8 . . 1
. 8 . . 1
3 8 1180.3883718426139 1 1
3 8 798.6087408547241 1 1
1 8 464.7010388680611 1 1
3 8 468.95684784737665 1 1
3 8 467.2475282928512 1 1
1 8 466.15778212467404 1 1
3 8 214.47010904794834 1 1
1 20 884.9662644840365 1 1
1 8 294.3567560487658 1 1
1 20 288.5516826092537 1 1
1 8 675.6600430626831 1 1
1 8 503.9832822063969 1 1
1 8 505.5631673075538 1 1
1 8 801.1122168545756 1 1
1 8 747.1919134859783 1 1
1 8 349.82689907040594 1 1
1 8 345.49722573262943 1 1
3 8 272.24022452728195 1 1
1 8 802.9849925585834 1 1
1 8 508.59884446780995 1 1
1 20 490.2383214499409 1 1
1 8 1054.9295443327912 1 1
3 8 467.2475282928512 1 1
3 20 717.2572717933948 1 1
1 8 508.59884446780995 1 1
2 20 759.6173097515931 1 1
. 8 . . 1
3 8 503.9832822063969 1 1
3 8 932.3155639493909 1 1
3 8 466.4212546881388 1 1
1 20 660.2565127367316 1 1
1 8 508.59884446780995 1 1
3 8 441.8603911421581 1 1
3 20 323.2276144512617 1 1
1 8 203.5601604460555 1 1
3 20 846.8875976572155 1 1
3 8 3530.0989736873325 1 1
1 8 349.82689907040594 1 1
1 8 751.2864089719695 1 1
3 8 274.80621657217915 1 1
1 8 274.80621657217915 1 1
. 8 . . 1
1 8 221.4399858408954 1 1
3 20 1144.9210019862383 1 1
1 20 239.30089340887912 1 1
3 20 356.97452468514854 1 1
2 20 801.4179752110739 1 1
. 20 . . 1
3 20 219.41748435316362 1 1
3 20 315.63561204512615 1 1
1 20 906.8118532129156 1 1
. 20 . . 1
1 8 206.10466235414506 1 1
3 20 289.7635610878877 1 1
0 8 0 . 1
2 8 622.9967044904541 1 1
. 8 . . 1
1 8 468.95684784737665 1 1
3 8 285.96014529727876 1 1
. 20 . . 1
1 8 749.5342081880037 1 1
. 8 . . 1
1 8 221.4399858408954 1 1
1 8 805.9225317999051 1 1
3 8 345.49722573262943 1 1
1 8 441.8603911421581 1 1
3 20 244.57715881885275 1 1
2 20 290.2621760316992 1 1
3 20 808.8773529095702 1 1
1 20 433.96218538651505 1 1
3 20 166.94871987137606 1 1
3 20 1316.0872401068013 1 1
. 8 . . 1
3 8 327.3039934743616 1 1
. 20 . . 1
1 8 203.5601604460555 1 1
. 8 . . 1
3 8 386.5859193626167 1 1
3 20 2806.5833698927854 1 1
end
label values c_act c_act
label def c_act 1 "Ocupado", modify
label def c_act 2 "Desocupado", modify
label def c_act 3 "No PEA", modify
label values id id
label def id 8 "Trim Jun-Jul-Ago19", modify
label def id 20 "Trim Jun-Jul-Ago20", modify


keep if res14==1
tab c_act id [iw=facpob] if pet==1 & id==8 | id==20 , matcell(freq) matrow(names)
matrix list freq

mat li freq , format("%3.1f")
putexcel set cuadro.xlsx, sheet("C_1") modify
putexcel D11 = matrix(freq), nformat(0.0)

[Andrés Lahur Talavera Cuya]

Is there the option?

 number_sep_d1

[William Lisowski]

Thank you, I see that in my previous post I overlooked the appendix with the Stata's nformat() "codes" for a few common formats. I was focused on the description of the nformat() option itself.

 nformat(excelnfmt) changes the numeric format of a cell range.  Codes for commonly used formats
    are shown in the table of numeric formats in the Appendix.  However, any valid Excel format
    is permitted.  Formats are formed from combinations of the following symbols.

                                               Cell      Fmt      Cell
 Symbol        Description                    value     code  displays
----------------------------------------------------------------------
 0             Digit placeholder (add zeros)   8.9      #.00      8.90
 #             Digit placeholder (no zeros)    8.9      #.##       8.9
 ?             Digit placeholder (add space)   8.9      0.0?       8.9
 .             Decimal point
 %             Percentage                       .1         %       10%
 ,             Thousands separator           10000     #,###    10,000
 E- E+ e- e+   Scientific format          12200000  0.00E+00  1.22E+07
 $-+/():space  Display the symbol               12     (000)     (012)
 \             Escape character                  3      0\!         3!
 *             Repeat character                  3        3*    3xxxxx
                      (fill in cell width)                         
 _             Skip width of next character   -1.2      _0.0       1.2
 "text"        Display text in quotes         1.23  0.00 "a"    1.23 a
 @             Text placeholder                  b   "a"@"c"       abc
----------------------------------------------------------------------

Stata's

nformat(number_sep_d2)

corresponds to the

nformat(#.##)

Thesre is no number_sep_d1 but it would correspond to

nformat(#.#)

You ask for a blank to be used as the thousands separator but that is not available. You can specify that a thousands separator is to be used by inserting a comma as shown above, but it is up to Excel what character is used for the thousands separator. If your copy of Excel uses something other than a comma, that is what will be presented, even (I think) if your format uses a comma.

[Andrés Lahur Talavera Cuya]

thank you