Hi,
When export to CSV I have following problem.
My original dataset in SAS before export (Ratio is fixed to $13.9):
Region | Type | Ratio |
CAN | Banana | 0.234236354 |
CAN | Apple | 0.706008361 |
CAN | Orange | 0.059755285 |
After exporting to CSV I get:
Region | Type | Ratio |
CAN | Banana | 0.2342363540200000 |
CAN | Apple | 0.7060083610000980 |
CAN | Orange | 0.0597552850000087 |
In the original dataset I know the sum is perfectly 1 but after exporting the dataset to csv the sum become
1.0000000000201100 |
I saw someone mention about floating point in excel CSV before but is there a way that we can avoid the rounding error when export to csv in SAS? Any suggestion will be appreciated!
I've tried "ODS csv file=" and "proc export data=" two methods.
Numerical precision issues.
Here's the SAS reference:
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrcon/p0ji1unv6thm0dn1gp4t01a1u0g6.htm
And here's the Excel reference:
https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision....
The precision of a number varies depending on the size of the mantissa. Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E-308; however, it can only do so within 15 digits of precision
The 15 digit precision is common in many applications.
What does "(Ratio is fixed to $13.9)" mean? If a variable is character, which would be a $ type format, then DECIMALS have no place in the format. If your value has a 13.9 format assigned the value in SAS could very well hold decimals you don't think are there but would be when the value is "exported". If you calculated the ratio value and assigned a format to the result this is extremely likely to be the case.
Example:
data forexporttest; x= 123; y= 57; ratio = y/x; put "with 13.9 format " ratio= 13.9; put "with best18. format " ratio= best18.; run; proc export data=forexporttest outfile='exporttest.csv' replace; run;
The Put statements show:
with 13.9 format ratio=0.463414634 with best18. format ratio=0.46341463414634
The resulting csv file in my Proc Export shows:
x,y,ratio 123,57,0.4634146341
And why does the value for ratio look like: 0.4634146341
Because when I examine the LOG for proc export we see a generated data step:
655 /********************************************************************** 656 * PRODUCT: SAS 657 * VERSION: 9.4 658 * CREATOR: External File Interface 659 * DATE: 03MAY21 660 * DESC: Generated SAS Datastep Code 661 * TEMPLATE SOURCE: (None Specified.) 662 ***********************************************************************/ 663 data _null_; 664 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ 665 %let _EFIREC_ = 0; /* clear export record count macro variable */ 666 file 'exporttest.csv' delimiter=',' DSD DROPOVER lrecl=32767; 667 if _n_ = 1 then /* write column names or labels */ 668 do; 669 put 670 "x" 671 ',' 672 "y" 673 ',' 674 "ratio" 675 ; 676 end; 677 set FOREXPORTTEST end=EFIEOD; 678 format x best12. ; 679 format y best12. ; 680 format ratio best12. ; 681 do; 682 EFIOUT + 1; 683 put x @; 684 put y @; 685 put ratio ; 686 ; 687 end; 688 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ 689 if EFIEOD then call symputx('_EFIREC_',EFIOUT); 690 run;
Which used the format BEST12. for the export. Which truncates/rounds the value of the repeating decimal value I generated in the data step. How you export the data could well be setting or using a different format that shows more digits than you looked at in SAS.
And your
In the original dataset I know the sum is perfectly 1 but after exporting the dataset to csv the sum become
1.0000000000201100 |
statement of "sum is perfectly 1" is almost not true, but the format that was used in SAS to look at the value likely rounded the value, especially if it only displayed 9 decimals.
You probably should also show just how you exported your data to CSV.
Hello @notepadd123
I don't know what your environment was, but I don't have the issue you are having.
I have tested the following code on SASOnDemand for Analytics.
/* Creating a dataset with your values */
data test;
input @1 Region $ @5 Type $ @11 Ratio;
datalines;
CAN Banana 0.234236354
CAN Apple 0.706008361
CAN Orange 0.059755285
;;
/*Exporting to csv*/
proc export data=test outfile="/home/mypath/test.csv";
run;
My result is as follows. I have attached the csv I generated.
I have opened the csv on a spreadsheet (google sheets, I am using Chromebook). It looks OK
If you are using excel ,then you may try changing the number format in your excel worksheet.
SAS stored numbers a floating point. The format attached to a number is how you want it DISPLAYED, it has nothing to do with how the values are stored. Decimal fractions cannot be exactly represented in binary floating point numbers (other than combinations of powers or two, like 0.5, 0.25, 0.125 ....)
You seemed to be saying your values are formatted to 9 decimal places. And the difference in the SUM you are showing is zero for more than 9 decimal places.
* 1.000 000 000 020 110 0 ;
So what is your issue?
If you want to debug more you need to provide more information.
What are the exact values in the SAS dataset before writing it to the CSV file? To see the exact values use the HEX16. format as that will show the actual hex codes from the 64 bit floating point number that SAS uses to store the number.
What are the exact value in the CSV file? Show the TEXT of the CSV file, do NOT touch the CSV file with Excel or any other spreadsheet program. Open it in a text editor or just type it to the terminal window.
How did you create the CSV file? Did you use some tool like PROC EXPORT? Some GUI push button tool in Enterprise Guide or SAS/Studio user interface? Or did you write it yourself using a data step with the DSD option on the FILE statement? What format was attached to the variable in question?
Hi,
I have a very similar issue. Essentially, the tables on the screen in SAS are, say, displayed as zeroes (0), yet whilst exporting them to CSV files (in case of our recipients the delimiter is a tilde, yet I checked also another basic delimiters like a pipe or semicolon) I see very small numbers saved in CSV as, say, "2E-9" instead of zeroes.
That format of figures (written as exponentials) can't be accepted by further inbound systems to which this CSV needs to be uploaded.
How to make sure that figures in the CSV file will be saved as figures to, say, six decimal places, as I rounded those up in my SAS table?
Many thanks!
Marzena
1) Start your own thread. As the creator of the thread you can then indicate a correct solution to your particular problem for others to find on the forum.
2) Reference this (or the other dozen or so related threads)
3) Provide code of how you create the CSV file, or at least very clearly describe how.
4) Provide example data, best in the form of working data step that replicates your issue.
5) Clearly describe what you expect as the result in the target CSV file.
And in very serious note, never trust what a spreadsheet displays when opening a CSV file. Depending on the particular software and values intended values can change types. And if you allow the spreadsheet software to save the csv file may permanently change the actual values.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.