Usage Note 34242: Exporting long values, that are all numbers, to Excel using ODS might result in scientific notation
When exporting values that are all numbers to Excel, by using the CSV, MSOffice2K, or ExcelXP destinations, the value might result in scientific notation. If a value consists of eleven or more numbers without an explicit Excel format applied, Excel uses the general format and displays the value in scientific notation. To avoid scientific notation, specify a custom Excel format that displays the value as desired. Alternatively, if the value is not needed for calculation, you can display the value using the text format.
If you supply a custom format with a number using the MSOffice2k or the ExcelXP destinations and the number is sixteen or more digits, the value after the fifteenth digit is displayed with zeroes. This is because of the way in which Excel stores numbers. Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only fifteen significant digits in a number, and changes digits after the fifteenth place to zeroes. If the value is not needed for a calculation, then it can be formatted as text, which displays up to 1,024 characters.
Operating System and Release Information
| SAS System | Base SAS | z/OS | 9.1 TS1M3 | |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.1 TS1M3 | |
| Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 | |
| Microsoft® Windows® for x64 | 9.1 TS1M3 | |
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 | |
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 | |
| Microsoft Windows 2000 Server | 9.1 TS1M3 | |
| Microsoft Windows 2000 Professional | 9.1 TS1M3 | |
| Microsoft Windows NT Workstation | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 | |
| Microsoft Windows XP Professional | 9.1 TS1M3 | |
| Windows Vista | 9.1 TS1M3 | |
| 64-bit Enabled AIX | 9.1 TS1M3 | |
| 64-bit Enabled HP-UX | 9.1 TS1M3 | |
| 64-bit Enabled Solaris | 9.1 TS1M3 | |
| HP-UX IPF | 9.1 TS1M3 | |
| Linux | 9.1 TS1M3 | |
| OpenVMS Alpha | 9.1 TS1M3 | |
| Solaris for x64 | 9.1 TS1M3 | |
| Tru64 UNIX | 9.1 TS1M3 | |
*
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.
/* Adds custom format to the variable longnum preventing the */
/* value from displaying in scientific format. */
data one;
longnum=123456789112345;
longnum1=123456789112345;
run;
ods tagsets.excelxp file="temp.xls";
proc print;
var longnum / style(data)={tagattr='format:#,###,###,###,####'};
var longnum1 ; /* Shows number in scientific format */
format longnum longnum1 best16.;
run;
ods tagsets.excelxp close;
/* Added text format preventing value from replacing data after the 15th digit */
/* with zeroes for a numeric field. Demonstrates how this smae field appears */
/* when a custom format is applied. */
data one;
longnum=12345678911234567;
longnum1=12345678911234567;
longnum2="12345678910123456789012345678";
run;
ods tagsets.excelxp file="temp1.xls";
proc print;
var longnum / style(data)={tagattr='format:@'};
var longnum1 / style(data)={tagattr='format:#,###,###,###,###,###'}; /* Displays number in scientific format */
var longnum2 / style(data)={tagattr='format:@'};
format longnum longnum1 best18.;
run;
ods tagsets.excelxp close;
| Date Modified: | 2009-01-20 15:15:08 |
| Date Created: | 2008-12-17 10:56:30 |
