08-03-2011 05:59 PM
Several of our most-used programs include statements to export to Excel, similar to this:
proc export data = mytable
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
format (list of columns and formats) ;
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,
08-04-2011 09:44 AM
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.
08-04-2011 08:48 AM
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 ;
08-04-2011 09:45 AM
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!
08-04-2011 10:04 AM
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.
Need further help from the community? Please ask a new question.