topic Re: SAS rounds Excel cell values on import in SAS Programming

SAS rounds Excel cell values on import

texasmfp — Thu, 19 Jan 2023 05:14:19 GMT

I am using a step that imports values from Excel:

PROC IMPORT OUT = SV_VAR_VALUES
    DATAFILE = "&SV_PATH" DBMS = EXCEL REPLACE;
    RANGE = "&SV_VALUE_RANGE"; 
    GETNAMES = NO;
RUN;

Some values are very small numbers (e.g.,0.000124688 and 0.007665256) which SAS rounds to 0.0001 and 0.0077.

Its not just displaying these with 4 decimal places, it is storing and using them at the 4-decimal place level. I know this because, if I instead multiply these by 1000 in the Excel file, and after importation, divide them by 1000, I do get the correct value. I also know this because when these imported values are multiplied by another value and, the result is different (and it is accurate with the *1000+ /1000 method). For example, if I have SAS multiply that imported value by 393, I get .0393 using the straight up method, but I get 0.0490 using the *1000+ /1000 method. I get different values still, if I multiply/divide by 10,000 or 100,000. SAS is definitely truncating the imported value to 4 decimal places. The Excel values are not hard-coded, but are cells with formulas if that matters.

Can someone explain why this happens and how to avoid the temporary workaround? Thanks

Re: SAS rounds Excel cell values on import

ballardw — Thu, 19 Jan 2023 05:16:27 GMT

SAS will assign a display FORMAT, typically a BEST of some flavor. That FORMAT may be doing rounding to fit that definition.

You can change the format to show more digits. Try a BEST16. with values in the range you are showing.

Also, when you deal with very small decimal values you can run into storage precision of how many digits can be stored in 8 bytes.

You can run this code and see some examples of displaying a value with different formats.

data example;
   file print;
   x = 0.0012345678;
   put x= best4.;
   put x= best6.;
   put x= best8.;
   put x= best10.;
   put x= best12.;
   put x= 16.14;
run;

The bit after the X= is the format assignment for how to display the value of X by that Put statement.

Re: SAS rounds Excel cell values on import

andreas_lds — Thu, 19 Jan 2023 05:59:05 GMT

@texasmfp wrote:

I am using a step that imports values from Excel:

[...]

Can someone explain why this happens and how to avoid the temporary workaround? Thanks

Workaround: Don't use excel as data source, use csv instead and write a data step to read the file. Or: have you tried using dbms=xlsx instead?

Re: SAS rounds Excel cell values on import

texasmfp — Thu, 19 Jan 2023 06:36:28 GMT

It turns out, the issue was how the values were formatted in Excel. The number was formatted as a dollar value; which SAS truncated to 4 decimals. When I switch it to straight number formatting in Excel, SAS imported it to 10 decimal places and carried that extended value through the calculations. Thanks Andreas_lds and ballardw for getting me thinking.