sas dataset coversion to csv

[HeatherNewton]

when I try to convert sas dataset into csv

numbers like -1,825,189,000,000,000. becomes -1.825189E15

how can I stop it happening when converting into csv?

[HeatherNewton]

oh sorry wrong question, actually in sas it is showing as -1.825189E15 since length is set to 8 only

now my question is what do I need to do in sas such that when it is converted to csv it is showing full number and not in scientific notation ?

[JosvanderVelden]

Have you read the sas documentation that explains numeric precision in SAS: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lepg/p0dv87zb3bnse6n1mqo360be70qr.htm#p0kfa23...?

[HeatherNewton]

I  dont get it I have length= 8 so should shown as 17digit integer but instead it gives me scientific notation

what has gone wrong

[Tom]

---

@HeatherNewton wrote:

I  dont get it I have length= 8 so should shown as 17digit integer but instead it gives me scientific notation

what has gone wrong

---

LENGTH determines how many bytes are used to store the value in the dataset.  Since numbers are stored as 64-bit floating point binary values you should use 8 bytes for the LENGTH.

How it prints is controlled by what FORMAT is used to display the value.

[ballardw]

---

@HeatherNewton wrote:

oh sorry wrong question, actually in sas it is showing as -1.825189E15 since length is set to 8 only

now my question is what do I need to do in sas such that when it is converted to csv it is showing full number and not in scientific notation ?

---

And what FORMAT is assigned to the variable???

Display is very much dependent on Format as well as length.

data example;
   x= 12345678912345678;
   put x= best3. ;
   put x= best4. ;
   put x= best5. ;
   put x= best12. ;
   put x= best20. ;
run;

Best tries to fit some display into the given number of positions. Why does the above show *** for the result of Best3.? Because even with scientific notation you need E plus 2 digits to indicate the approximate value which does not leave anything for the coefficient which appears before the E.

I suspect when you look at the format for your variable you will find that it is a BEST12. (I counted the characters in your example value). Change of format will yield a different displayed value.

[HeatherNewton]

I dont mean proc print, in output data it is showing as -1.825189E15

I searched all sas program, no formatting is done to this variable

[Kurt_Bremser]

By default, SAS uses the BEST12. format for numeric variables. When numbers are too large for 12 digits, the format switches to scientific notation to fit the number into the available space.

When you count the characters in -1.825189E15, you will end with 12.

You need to apply a suitable format before or during the export.

You have still failed to show your export code. Please do so NOW.

[HeatherNewton]

I was using the following:
libname thrznov "mpoint/baselii_30nov";
libname thrznov "mpoint/test";

data final;
set thrznov.final (encoding=any);
run;
proc export data=final outfile='/mpoint/test/final" dbms=csv replace;run;

so it will work if I add format column_name best32. after 'set thrzbiv.final ....' line?

[Kurt_Bremser]

---

@HeatherNewton wrote:

so it will work if I add format column_name best32. after 'set thrzbiv.final ....' line?

---

Yes.

Since your second LIBNAME overrides the first, the first one is not necessary.

[HeatherNewton]

data test;
input sci;
datalines;
1.825189E15
8.796093E14
3.469447E-19
;
run;

proc print data=test;
run;

data test2;
set test;
format sci best32.;
run;
 
 proc print data=test2;
 run;

I tried this, the 3rd one still give scientific notation, why??

[Kurt_Bremser]

From the documentation of the BESTw. format (Maxim 1!):

• Extreme values and values with leading or trailing 0s might be written in scientific notation to fit into the specified width, to increase the precision, or to simplify the magnitude of the number. Extremely small values might be written as 0 if the width is too small for scientific notation.

(emphasis by me)

Your output is the result of BESTw. trying to make numbers as human-readable as possible. When having numbers of wildly different magnitude in one column, the scientific notation is most helpful.

[Tom]

Because that is what the BEST format does.

If you know the values are integers then do not use BEST.  Just use normal numeric format

format sci 32.;

Or you can use F as an alias for the normal numeric format.

format sci F32.;

It doesn't matter if the width is more than your data needs since leading (and trailing) spaces are removed from the values written to a delimited file.

But what was wrong with leaving the value in scientific notation? What tool are you using to read the CSV?  Why can't it read such a normal way to express a number?

[HeatherNewton]

Hi Tom, thank you for your explanation.

later I find out DB2 can actually take scientific notation as number when read from csv. It was only the length of data type not correct. 

[Patrick]

---

@HeatherNewton wrote:

I dont mean proc print, in output data it is showing as -1.825189E15

I searched all sas program, no formatting is done to this variable

---

And as others told you already: If you don't apply a format to a numerical variable then default format best12 gets used. If you have a number with 15 digits then this format prints the number in scientific notation. 

You need to explicitly apply a different format for these numerical values. I'd be using best32.