topic Re: Proc export to Excel now loses currency format in SAS Procedures

Proc export to Excel now loses currency format

JSWilson64 — Wed, 03 Aug 2011 21:59:30 GMT

Several of our most-used programs include statements to export to Excel, similar to this:

proc export data = mytable

outfile="c:\mypath\myfile.xls"

dbms=excel2000 replace;

sheet=exported;

run;

The data sets often contain numeric fields that are formatted in SAS as currency, most commonly "Dollar17.2". In the past, the export has worked fine, and the resulting spreadsheet has columns formatted as currency where the SAS data was formatted that way.

I recently upgraded from SAS 9.2 TS1M0 to 9.2 TS2M3 as our department's guinea pig, and now when I run the same proc export statements, the columns that are formatted in SAS as Dollar17.2 come into Excel unformatted.

I have also tried exporting using a libname / data step, like this

LIBNAME

WrkBk

EXCEL

'c:\myfolder\myfile.xls'

VER=2000;

Data wrkbk.tabname;

format (list of columns and formats) ;

set mytable;

run;

but alas, this also does not work. And I've tried playing with the Excel version (2000/2003/2007) to no success.

Is there a hot fix I'm not aware of that could remedy this situation? Any other advice? If I can't get this to work, I can't recommend my co-workers upgrade to TS2M3, which we REALLY want to do so we can export some of our larger data sets into Excel (now with over one milllion rows in 2007/2010) rather than Access.

Thanks for any help,

James Wilson

Proc export to Excel now loses currency format

Doc_Duke — Thu, 04 Aug 2011 00:40:39 GMT

Have you contacted tech support? This is something that they ought to know about to get it into the fix list.

Proc export to Excel now loses currency format

Ksharp — Thu, 04 Aug 2011 10:13:28 GMT

Did you try ods tagsets.excelxp or other ods destinations?

Ksharp

Proc export to Excel now loses currency format

art297 — Thu, 04 Aug 2011 12:17:42 GMT

It appears to work correctly with dbms=xls, but it does appear to be something that tech support should quickly be brought to the attention of tech support.

Re: Proc export to Excel now loses currency format

data_null__ — Thu, 04 Aug 2011 12:48:37 GMT

The LIBNAME method can be made to work using the MSENGINE option on the LIBNAME statement.

LIBNAME WrkBk EXCEL 'mytestfileJET.xls' VER=2000 MSENGINE=JET ;

Data wrkbk.shoes01;

set sashelp.shoes;

run;

libname wrkbk;

Re: Proc export to Excel now loses currency format

JSWilson64 — Thu, 04 Aug 2011 13:44:32 GMT

I haven't tried it using the ODS ExcelXP tagset - we have had mixed results using this method, and it would require rewriting a lot of programs to implement.

Re: Proc export to Excel now loses currency format

JSWilson64 — Thu, 04 Aug 2011 13:45:26 GMT

OK, that works, so it's a start. We would prefer to use Proc Export but this could be the way we have to go with 9.2 phase 2.

I will be contacting tech support to see if they're aware of the issue. Thanks!

Re: Proc export to Excel now loses currency format

art297 — Thu, 04 Aug 2011 13:56:32 GMT

Since you would prefer to use proc import, as I mentioned in a previous response, use proc import with DBMS=xls

Re: Proc export to Excel now loses currency format

JSWilson64 — Thu, 04 Aug 2011 14:04:45 GMT

Thanks - I had tried that method but it didn't work. It helps if you spell everything correctly. :smileylaugh:

Tried it again and it preserved the currency formats.