Proc export to Excel now loses currency format

[JSWilson64]

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

[Doc_Duke]

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

[Ksharp]

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

Ksharp

[art297 · Accepted solution]

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.

[JSWilson64]

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.

[data_null__]

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;

[JSWilson64]

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!

[art297]

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

[JSWilson64]

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.