Desktop productivity for business analysts and programmers

Excel export format issue

Posts: 45

Excel export format issue



I have an issue with some numbers which represent amounts while exporting in Excel like this:

proc export data=INF_SUM
dbms=xls replace;

In my sas table INF_SUM I have amounts like for example "43.87" whereas on ggg.xls it is transformed like "43.86895", which don't represent an amount anymore if you don't manually ask Excel to transform the column concerned.


Do you know I can do to keep formats?

I work with Excel 2010 and SAS Enterprise Guide 7.1


Thank you for help

Super User
Posts: 10,530

Re: Excel export format issue

If you want the numbers rounded to .01, you should do so before exporting. SAS exports numbers as raw values to not lose data (dates and datetimes usually retain date formatting).

data INF_SUM;
set INF_SUM;
amount = round(amount,.01);

I you don't want to round, you can always export to a text format (csv) in a data step and use the proper format in the put statement.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,799

Re: Excel export format issue



Just to add, if you want nice looking Excel output then rather than use Export which is pretty basic, use ods tagsets.excelxp:


You can specify Excel formats and such like. 

Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation