numeric values convert to exponential value if there is a short numeric format

[Mohan_Rang]

Hi SAS experts,

I have a problem with numeric values getting converted into exponential format automatically when length of the values in a numeric column exceed the format length. This happens when I use the code shown below - I'm trying to export a SAS huge dataset directly into a CSV file 

Are there any generic options for the PUT statement in the data step shown below which can prevent the numeric values from converting to exponential values. I don't want to use explicit format(put function) for the numeric variable in the data step. Please provide your suggestions.

data _null_;

set dsn;

file "/staging/csv/test.csv" dlm=',' dsd lrecl =32000;

put (_all_) (+0);

run;

[Reeza]

What do you want to happen if the number is too big to display in the sort length then?

[Mohan_Rang]

The original values should be displayed.
For eg. if the numeric column length is 8 and the format is 5. and the value is -24671, I want SAS to show the original values -24671 instead of -24E3.

[Reeza]

The - sign takes up a position (as do decimals and percentages (2)) so you don't have 5 slots there for the 5 digits plus the minus sign, that would require 6. So how would you like to handle that?

[Mohan_Rang]

I know a format put(col,6.) on the numeric column would resolve this issue. I’m looking for options in the PUT statement in the data _null_ step below or any other generic solution in the data step.

data _null_;
set dsn;
file "/staging/csv/test.csv" dlm=',' dsd lrecl =32000;
put (_all_) (+0);
run;

[Patrick]

---

@Mohan_Rang wrote:
I know a format put(col,6.) on the numeric column would resolve this issue. I’m looking for options in the PUT statement in the data _null_ step below or any other generic solution in the data step.

---

The length of a numerical variable defines the bytes used for storing a number. A length of 8 can store a number with up-to 15 digits with full precision so the length doesn't tell you how "big" the numbers are.

The FORMAT defines how numbers get written. If the format applied to a numerical variable is too short to print the number then scientific notation will get used.

Use a format statement to change the formats for variables in case they are too short. If you need to change the formats for a whole list of vars like for all vars with a name starting with col... then you can also use a wildcard as done in below code for var1 and var2.

data have;
  format var1 best6.;
  var1=10000000000;
  var2=var1;
run;

data _null_;
  file print;
  set have;
  format var: 32.;
  put (_all_) (+0);
run;

[Mohan_Rang]

This might affect the other decimal values or date values. Is there any logic that we can write that if exponential values are found then format them to original values?

[Tom]

Are you asking how to tell if the format attached to a variable is too short for the values stored in the variable?

Why not just create the dataset with proper formats attached before trying to write it to text?

If you want to remove or override the formats attached before writing the text file but also keep the formats that are actually NEEDED then check out the FMTINFO() function.  You can use the CAT info to let you know which variables have DATE, TIME or DATETIME type formats attached to them.

Example:

data dsn;
  length id $8 amount date 8;
  format amount 5. date date9.;
  input id amount date :date.;
cards;
1 123456 01JAN2021
;

proc contents data=dsn noprint out=contents; 
run;

proc sql noprint;
%let formats=;
select
   case when fmtinfo(format,'cat') in ('date','time','datetime') then 
   catx(' ',nliteral(name),cats(format,formatl,'.',case when formatd then cats(formatd) else ' ' end))
   else ' ' end
  into :formats separated by ' '
from contents
where format ne ' ' or formatl > 0 
;
quit;

data _null_;
  set dsn;
  format  _numeric_ best32. &formats;
  file log dsd;
  put (_all_) (+0);
run;

179   data _null_;
180     set dsn;
181     format  _numeric_ best32. &formats;
182     format &formats;
183     file log dsd;
184     put (_all_) (+0);
185   run;

1,123456,01JAN2021

[Mohan_Rang]

Thanks for the example. I will try this out.

[ChrisNZ]

> Is there any logic that we can write that if exponential values are found

Not too sure what you are after tbh, but this test can be done using the vvalue() function.

For example 

if index(vvalue(VAR),'E') then ...

[Mohan_Rang]

Thank you. Yes, this might work too. I have to try this out.