SAS table exported to CSV file saves +-zeroes as strings with E (exponentials) instead of zeroes

[MarzenaKurowska]

Hi,

my tables in SAS window correctly display some very small numbers as zeroes (0) --> since I have rounded them previously in SAS code to six decimal places. Yet whilst exporting this table to CSV file I see very small numbers saved in CSV as strings, say, "2E-9" instead of zeroes.

Such a 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 my CSV file will be saved as figures to, say, six decimal places, exactly as I rounded those up in my SAS table, also including these zeroes - to avoid saving those almost zeroes as strings with exponentials?

Many thanks!

Details:

• I am using SAS VIYA.
• delimiter to be used is a tilde, yet I checked also other basic delimiters like a pipe or semicolon and the result is the same

• below a part of the code where I am rounding figures to 6 decimal places (some very small figures aptly appear as zeroes in SAS VIYA): 

proc sql;
create table &Currentrun. as select
Ultimo format=YYMMDD10.0 as Ultimo
, round(_%eval(&JJJJ.+0)_0_CVAR,.000001) as CVAR_1
, round(_%eval(&JJJJ.+0)_0_EAD,.000001) as EAD_1

from out.&Currentrun._trans ;
quit;

• below a part of the code where I export the data to a CSV file: 

PROC EXPORT
DATA= &Currentrun.
OUTFILE= "&Path./&JJJJ.&MM./&subpath./Example_file.csv"
DBMS=CSV
REPLACE;
delimiter='~';
RUN;

[Tom]

How did you LOOK at the CSV file?

Did you accidentally let some spreadsheet program, like Excel, open it?  If so the spreadsheet will make its own decisions about how to display numbers.

Do you see the scientific notation when you look at the text of the CSV file using a text editor? Or just using cat or type to display the file to the terminal/console window?

Which variable is being written that way?

Why not attach a different FORMAT to that variable before creating the CSV file?  Just like you attached the YYMMDD10. format to the date variable.

You could either just REMOVE any format that VIYA and/or PROC SQL might have mistakenly attached to the variable.  Or attach one that has enough decimal places for the values you have.

data test;
  input x;
  best32 = x;
  f12p9 = x;
  format best32 best32. f12p9 f12.9 ;
cards;
2e-9
;

proc print;
run;

[FreelanceReinh]

Hi @MarzenaKurowska,

Your approach should work. A number as small as 2E-9 (or even 0.49999E-7) should definitely be rounded to an exact zero by the ROUND function with rounding unit .000001. An exact zero, in turn, should definitely be written as such to the CSV file by PROC EXPORT. So, if your code was executed without errors (see the log) and you look at the right place in the correct CSV file using an appropriate tool, there should be nothing like "2E-9".

Otherwise, my first step to investigate this would be to look at some values of CVAR_1 and EAD_1 in dataset &Currentrun.:

proc print data=&Currentrun;
where ID=...; /* enter a suitable condition */
format cvar_1 ead_1 hex16.;
var ID cvar_1 ead_1;
run;

(with and without the above FORMAT statement) with a suitable WHERE condition using one or more ID variables to isolate a few observations whose CVAR_1 or EAD_1 values appear as "2E-9" in the CSV file.