Rounding issue when exporting as a csv

[Amanda_Lemon]

Hi all, 

I came across an issue of unexpected rounding when exporting a dataset to a csv file. Specifically, I am using proc export; then when checking the csv file, the number of decimals is not what it was before exporting for some values. For example, 10.895 is exported as 10.9, 45.42 as 45.4. This happens only to some values, meaning that other values are exported correctly. I was wondering if there is a way to stop SAS from rounding when exporting? 

Thanks in advance!

[FreelanceReinh]

Hi @Amanda_Lemon,

---

@Amanda_Lemon wrote:

(...) when checking the csv file, the number of decimals is not what it was before exporting for some values.

---

How did you "check" the CSV file? If you (accidentally) did not use a text editor, e.g., Notepad, but spreadsheet software such as MS Excel to open the CSV file, the values you saw might be different from what is really stored in the file. Even worse, saving the file with Excel might have even changed the values permanently (so you may want to repeat the PROC EXPORT step).

I think for numeric variables PROC EXPORT uses the format that is associated with the variable in the dataset (if any) to write it to the CSV file. So, a value 10.895 in a variable formatted with, say, COMMA8.1 would indeed end up as 10.9 in the CSV file. If no format was associated with the variable, PROC EXPORT uses BEST12. as the default format. This would round a long number like 1234567890.12 to 1234567890.1, but not the much shorter values you mention.

If you are in doubt about the formats: PROC EXPORT writes a DATA _NULL_ step to the log. Please check the FORMAT statements it contains for the numeric variables in question, e.g.,

format variable_name best12. ;

Existing formats causing the unwanted rounding could be removed in a preliminary DATA step:

data want / view=want;
set have;
format variable1 variable2 variable3;
run;

proc export data=want
...

Alternatively, you could use a DATA step (similar to that written to the log by PROC EXPORT) instead of PROC EXPORT to create the CSV file.

[Tom]

How did you check the CSV file?

PROC EXPORT should not change the values, but it will have to convert numbers into text to be able to make a text file, so the formats that are attached to the value will determine how the number is converted to text.

So for example if you run this program:

data have;
  input id value;
cards;
1 10.895 
2 45.42
;

filename csv temp;
proc export data=have file=csv dbms=csv;
run;

The CSV file you get looks like this:

id,value
1,10.895
2,45.42

But if you attach the format 5.1 to the VALUE variable then it will look like this:

id,value
1,10.9
2,45.4

Or if you attach the format Z8.4 you will instead get:

id,value
1,010.8950
2,045.4200