topic Re: Deal with rounding problem while exporting to CSV in SAS Procedures

Deal with rounding problem while exporting to CSV

notepadd123 — Tue, 04 May 2021 18:07:27 GMT

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.

Re: Deal with rounding problem while exporting to CSV

Reeza — Tue, 04 May 2021 18:34:04 GMT

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-aka-why-does-excel-give-me-seemingly-wrong-answers/#:~:text=The%20precision%20of%20a%20number,within%2015%20digits%20of%20precision.

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.

Re: Deal with rounding problem while exporting to CSV

ballardw — Tue, 04 May 2021 19:23:27 GMT

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.

Re: Deal with rounding problem while exporting to CSV

Sajid01 — Fri, 07 May 2021 19:34:33 GMT

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.

Re: Deal with rounding problem while exporting to CSV

Tom — Fri, 07 May 2021 21:00:28 GMT

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?

Re: Deal with rounding problem while exporting to CSV

MarzenaKurowska — Wed, 31 Jan 2024 15:01:54 GMT

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

Re: Deal with rounding problem while exporting to CSV

ballardw — Wed, 31 Jan 2024 15:38:47 GMT

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.