Preserved numerical value when exporting to xls

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Preserved numerical value when exporting to xls

Hi Experts

 

Need some help please , I am trying to export numerical data to excel /xslb.

 

I have tried to format the field with BEST/NUMX with no luck.

 

The numerical data is standard 16 digits e.g 1111222233334444.

when I export using the below export code :

 

proc export outfile="Location\&TEST_NO._&TRIM_FROM._to_&TRIM_TO._&NOW..xlsb"
dbms=excelcs data = SDR_TXSET replace;
sheet="DATA";

quit;

 

when I open the excel sheet the data displays like : 1,2226543+13 & when I expand and format the cell the data displays like 

e.g 111122223334440 . which is incorrect.

 

Regards

 


Accepted Solutions
Solution
‎11-21-2017 01:46 AM
Super User
Posts: 9,925

Re: Preserved numerical value when exporting to xls

Posted in reply to Allistair

Since precision like that is not needed for statistics (the range of exponents in the real format is sufficient for either very large or very small values), this is usually no problem. Very often such large numbers are identification codes or such, and are therefore stored as character.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,925

Re: Preserved numerical value when exporting to xls

Posted in reply to Allistair

Because of the 8-byte real format that SAS uses for storing numeric data, the precision is limited to ~15 decimal digits. What you see is normal behaviour for data in SAS datasets.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 42

Re: Preserved numerical value when exporting to xls

Posted in reply to KurtBremser

Thank you for the reply - is there any way around this ? diff method ?

Thanks.

Solution
‎11-21-2017 01:46 AM
Super User
Posts: 9,925

Re: Preserved numerical value when exporting to xls

Posted in reply to Allistair

Since precision like that is not needed for statistics (the range of exponents in the real format is sufficient for either very large or very small values), this is usually no problem. Very often such large numbers are identification codes or such, and are therefore stored as character.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 93 views
  • 2 likes
  • 2 in conversation