CODEWITHSUNDEEP
sas
Victor
Victor

Reputation: 17077

SAS proc export to excel converts numbers to scientific notation

I have a SAS data set with a Number field. When I do proc export dbms=xlsx, it converts the number to scientific notation. Now I know I can change the number to a text in SAS and that will preserve the format as desired. But changing it to text creates issues with joins because it needs to join to another table where the column is numeric. In short, I am not too keen on changing the format using put(Colname,z5.) But I want to show in excel as a text...if I cannot get leading zeroes, at least I don't want the scientific notation. Is it possible?

Upvotes: 0

Views: 3457

Answers (2)

Richard
Richard

Reputation: 27498

  • Proc EXPORT DBMS=XLSX output

    • Does NOT produce formatted values in the target. The raw values will appear.
      • The numeric format is Excel General, so values >= 1E+11 will be displayed in scientific notation.
    • Does MAINTAIN SAS date formatted variable values as Excel date values formatted similarily to the original SAS date format.

  • ODS EXCEL output

    • Does produce formatted values in the target.
    • Allows customized Excel rendering with style option tagattr=

Example:

proc format;
  value $lettered
    'A'-'B' = 'Aaaaaaaaaa'
    'B'-'C' = 'Bbbbbbbbbb'
    'C'-'D' = 'Cccccccccc'
    'D'-'E' = 'Dddddddddd'
    'E'-'F' = 'Eeeeeeeeee'
  ;
;

data class(keep=id date: name: x age);
  format id z9.   
    date1 date9.
    date2 mmddyy10.
    date3 yymmdd10.
    date4 ddmmyy10.
    name  $10.
    name2 $lettered.
    x 9.4
  ;

  set sashelp.class;

  id + 1;
  date1 = today() - id;
  date2 = date1;
  date3 = date2;
  date4 = date3;

  name2=name;

  x = rand('uniform', 100);
run;

proc export data=class dbms=xlsx replace file='c:\temp\export.xlsx';
run;


ods excel file='c:\temp\print.xlsx';
proc print noobs data=class;
run;
ods excel close;


options noxwait xsync xmin;

%sysexec start "Preview" /D C:\Temp export.xlsx;
%sysexec start "Preview" /D C:\Temp print.xlsx;

Excel from PROC Export - Some date value formatting similar to original SAS date formatting, otherwise raw data values

enter image description here

Excel from ODS Excel & PROC Print - SAS date/custom/numeric formats in output

enter image description here

Upvotes: 1

Chris J
Chris J

Reputation: 7769

Use PROC REPORT and ODS instead.

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

If you wanted to force a particular variable to Excel Text, add the below to the PROC REPORT :

  define numvar / style={tagattr='format:text'} ;

https://support.sas.com//rnd/base/ods/templateFAQ/office91.pdf

Upvotes: 1

Related Questions