Problem Note 51083: Decimal numbers might be generated as large exponent numbers with the XLSX driver
When you use the XLSX driver to export data to Microsoft XLSX files, decimal numbers might be generated as large exponent numbers. The following example shows the error:
data temp1;
input mynumber;
datalines;
99.9
99.99
99.999
99.9999
99.99999
99.999999
99.9999999
99.99999999
99.999999999
99.9999999999
99.99999999991
99.999999999991
99.99999999999
99.999999999999
999.9
999.99
999.999
999.9999
999.99999
999.999999
999.9999999
999.99999999
999.999999999
999.9999999999
999.99999999991
999.999999999991
999.99999999999
999.999999999999
9999.9
9999.99
9999.999
9999.9999
9999.99999
9999.999999
9999.9999999
9999.99999999
9999.999999999
9999.9999999999
9999.99999999991
9999.999999999991
9999.99999999999
9999.999999999999
;
proc export data=temp1 outfile='c:\sastest\mydecimals.xlsx' dbms=xlsx replace;
sheet='mysheet';
run;
The resulting Excel file has the following numbers in it. There are also several numbers without a decimal point that are incorrect.
mynumber
99.9
99.99
99.999
99.9999
99.99999
99.999999
99.9999999
99.99999999
100
100
100
1E+19
1E+15
1E+19
999.9
999.99
999.999
999.9999
999.99999
999.999999
999.9999999
1000
1000
1E+16
1E+20
1E+20
1E+20
1E+20
9999.9
9999.99
9999.999
9999.9999
9999.99999
9999.999999
10000
10000
1E+17
1E+21
1E+21
1E+20
1E+21
10000
The problem occurs because the driver does not place a decimal point when the data precision is very close to a whole number with the power of 10, such as 100, 1000, 10000. To circumvent the problem, use the DBMS=EXCEL or DBMS=EXCELCS drivers to create the files.
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to PC Files | Microsoft® Windows® for x64 | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Microsoft Windows 8 Enterprise 32-bit | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Microsoft Windows 8 Enterprise x64 | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Microsoft Windows 8 Pro 32-bit | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Microsoft Windows 8 Pro x64 | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Microsoft Windows Server 2008 R2 | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Microsoft Windows Server 2008 for x64 | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Microsoft Windows Server 2012 Datacenter | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Microsoft Windows Server 2012 Std | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Windows 7 Enterprise x64 | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Windows 7 Professional x64 | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| 64-bit Enabled AIX | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| 64-bit Enabled Solaris | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| HP-UX IPF | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Linux for x64 | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
| Solaris for x64 | 9.4 | 9.4_M1 | 9.4 TS1M0 | 9.4 TS1M1 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
Decimal numbers might be generated as large exponent numbers when using the XLSX driver to generate XLSX files. The problem occurs when the decimal is very close to a round number.
| Type: | Problem Note |
| Priority: | alert |
| Date Modified: | 2013-09-20 16:11:16 |
| Date Created: | 2013-09-17 12:30:02 |
