Precision loss when exporting proc report with ods excel

[js5]

While trying to verify the programmed ODS report by comparing it to calculations done in excel directly, it was pointed out to me that the result are almost always a tiny bit different. I can provide example code if needed, but the general issue is: proc report contains values in percent9.2 format, which are exported to excel using ods excel. While the format with two decimal places is maintained, the underlying number is reduced in precision: 0,445772556636722 is what is stored in the SAS dataset, but only 44,57725566% is getting exported to excel. While one could argue that the difference is insignificant, it is nevertheless a loss of precision and causes comparison with Excel calculation to fail unless rounding is incorporated.

The value itself is pre-calculated by proc sql, proc report is only used to show the value. I tried changing the format of the variable on the dataset going into proc report to best32. with proc datasets, but the numbers are still exported with reduced precision. Is there anything which can be done about this? Thank you for the advice in advance. 

[Cynthia_sas · Accepted solution]

Hi:
I do not think this is PROC REPORT, per se, doing the truncation. I think you are running into general numeric precision issues. You might want to open a track with Tech Support on this. Do note that Excel has issues in general with numeric precision aside from any issues interacting with SAS. Per this Google search:

and, here's my test without involving SAS:

1) Opened Excel and started a new sheet and typed THIS number:

Note how my number ends in '234'. Immediately when I click the check, Excel immediately drops the 4 and in the upper window shows me 0.445772556636723, and in the cell, after the check only shows me 0.445772556636723. Then I go to format the cell and while I increase the decimal indicator size in the format window, if I go beyond 15, I see that my number is now ending in 0 and not ending in 4:

And then after my format cells, the number in the cell is now:

So without involving SAS at all, this is how Excel can change a big number or a number with a lot of decimals.

It seems to me you have 2 issues -- how does the number appear in SAS and how is that number internally stored in SAS and in SAS output. Then, how does Excel treat the number you send it. And when you send a number from SAS, are you going to automatically run into the built-in Excel limitations.

For more insight into how SAS deals with floating point representation of numbers, I recommend this documentation topic: https://go.documentation.sas.com/?docsetId=lrcon&docsetTarget=p0ji1unv6thm0dn1gp4t01a1u0g6.htm&docse... -- it has some good examples of numeric precision issues with integer numbers and then has this statement

  I note that your original number has 15 decimal places -- it seems to me that there are numeric precision issues that you can encounter with SAS when a number has so many decimal places, so you need to figure that out first, separate from what happens to that number when it gets transmitted to Excel.

  Then removing PROC REPORT from the mix, let's see what SAS does with the original number in the PDV, as shown below:

If I just use BEST32 to display the number, I only see 14 decimal places, NOT your original 15. So this is not a PROC REPORT issue. I believe you are running into a numeric precision issue with your fractional numbers. So, you have one issue with SAS and displaying 14/15 decimal places and a different issue with Excel.

  For any workarounds, you'd really need to work with Tech Support.

Cynthia

[ballardw]

@Cynthia_sas it appears that the links and examples didn't make it to your post. Or I can't see them for some reason.

[Cynthia_sas]

Sorry, I went back and filled them in with some screen shots.
Cynthia