topic Re: sas dataset coversion to csv in SAS Programming

sas dataset coversion to csv

HeatherNewton — Fri, 21 Jul 2023 06:48:23 GMT

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?

Re: sas dataset coversion to csv

HeatherNewton — Fri, 21 Jul 2023 08:07:03 GMT

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 ?

Re: sas dataset coversion to csv

JosvanderVelden — Fri, 21 Jul 2023 08:33:24 GMT

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#p0kfa236d94cv1n1j93z8oyuoksp?

Re: sas dataset coversion to csv

HeatherNewton — Fri, 21 Jul 2023 08:47:17 GMT

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

Re: sas dataset coversion to csv

_Manhattan — Fri, 21 Jul 2023 09:01:54 GMT

data your_data;
numeric_value = -1.825189E15;
run;

ods excel file="path/to/output.xlsx" ;
proc report data=mydata ;
  columns _ALL_ ;
run ;
ods excel close ;

You could try ods excel in combination with e. g. proc report instead of proc export, but I am not sure if that will produce your desired result. Could you provide some example code and data?

Re: sas dataset coversion to csv

JosvanderVelden — Fri, 21 Jul 2023 09:11:36 GMT

Just run the code below and analize the csv file. You may need to change the filename statement.

filename csvfile '~/csvfile.csv' encoding=utf8;

data _null_;
   file csvfile;
   largenumber = -1825189000000000;
   largenumber_fmt = largenumber;
   format largenumber_fmt comma32.;
   put "not formatted " largenumber=;
   put "format comma32. " largenumber_fmt=;
   largenumber + -9999999999999999;
   largenumber_fmt = largenumber;
   put "What if largenumber = largenumber + -9999999999999999?";
   put "not formatted " largenumber=;
   put "format comma32. " largenumber_fmt=;   
   positivelargenumber = 9007199254740992;
   positivelargenumber_fmt = positivelargenumber;
   format positivelargenumber_fmt comma32.;   
   put "not formatted " positivelargenumber=;
   put "format comma32. " positivelargenumber_fmt=;   
   positivelargenumber + 1;
   positivelargenumber_fmt = positivelargenumber;
   put "What if positivelargenumber + 1?";
   put "not formatted " positivelargenumber=;
   put "format comma32. " positivelargenumber_fmt=;   
run;

Re: sas dataset coversion to csv

ballardw — Fri, 21 Jul 2023 15:09:44 GMT

@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.

Re: sas dataset coversion to csv

Tom — Fri, 21 Jul 2023 16:15:22 GMT

What FORMAT did you attach to the variable?

If you do not attach any format then SAS will use BEST12. Which is what it looks like was used.

To have it print all of those digits you will need to use the normal numeric format with a width of at least 17.

If you want it to print like the example in your question then you would need to attach the COMMA format. With a width of at least 22.

Note that if you do use the COMMA format then the CSV file will add quotes around the value to protect the embedded delimiters.

data have;
  id +1 ;
  input number ;
cards;
-1825189000000000
;

697  data _null_;
698    set have;
699    file log dsd ;
700    put id number;
701    put id number :17. ;
702    put id number :comma22. ;
703  run;

1,-1.825189E15
1,-1825189000000000
1,"-1,825,189,000,000,000"
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: sas dataset coversion to csv

Reeza — Fri, 21 Jul 2023 16:20:48 GMT

Use formats

Re: sas dataset coversion to csv

Quentin — Fri, 21 Jul 2023 17:54:55 GMT

There are lots of ways to export a dataset to CSV. Which are you using? Can you post a full, reproducible example? Something like:

data have ;
  x=  -1825189000000000 ;
run ;

*your code to export to CSV here ;

Re: sas dataset coversion to csv

Tom — Sat, 22 Jul 2023 02:27:41 GMT

@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.

Re: sas dataset coversion to csv

HeatherNewton — Mon, 24 Jul 2023 04:40:42 GMT

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

Re: sas dataset coversion to csv

Kurt_Bremser — Mon, 24 Jul 2023 05:58:19 GMT

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.

Re: sas dataset coversion to csv

Patrick — Mon, 24 Jul 2023 06:22:22 GMT

@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.

Re: sas dataset coversion to csv

HeatherNewton — Mon, 24 Jul 2023 07:20:20 GMT

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?

Re: sas dataset coversion to csv

Kurt_Bremser — Mon, 24 Jul 2023 07:48:35 GMT

@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.

Re: sas dataset coversion to csv

HeatherNewton — Mon, 24 Jul 2023 07:54:55 GMT

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??

Re: sas dataset coversion to csv

Kurt_Bremser — Mon, 24 Jul 2023 08:18:58 GMT

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.

Re: sas dataset coversion to csv

Tom — Mon, 24 Jul 2023 13:21:29 GMT

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?

Re: sas dataset coversion to csv

HeatherNewton — Sat, 29 Jul 2023 01:23:56 GMT

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.