BookmarkSubscribeRSS Feed
fabdu92
Obsidian | Level 7

Hi,

 

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

proc export data=INF_SUM
outfile="/data/appli/file/ggg.xls"
dbms=xls replace;
sheet="catalog";
run;

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

2 REPLIES 2
Kurt_Bremser
Super User

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);
run;

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

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

http://support.sas.com/resources/papers/proceedings13/316-2013.pdf

 

You can specify Excel formats and such like. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 707 views
  • 2 likes
  • 3 in conversation