Help using Base SAS procedures

Proc export to Excel now loses currency format

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Proc export to Excel now loses currency format

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


Accepted Solutions
Solution
‎08-04-2011 08:17 AM
PROC Star
Posts: 7,491

Proc export to Excel now loses currency format

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.

View solution in original post


All Replies
Trusted Advisor
Posts: 2,116

Proc export to Excel now loses currency format

Posted in reply to JSWilson64

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

Super User
Posts: 10,044

Proc export to Excel now loses currency format

Posted in reply to JSWilson64

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

Ksharp

Solution
‎08-04-2011 08:17 AM
PROC Star
Posts: 7,491

Proc export to Excel now loses currency format

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.

Occasional Contributor
Posts: 18

Re: Proc export to Excel now loses currency format

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.

Respected Advisor
Posts: 3,799

Re: Proc export to Excel now loses currency format

Posted in reply to JSWilson64

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;

Occasional Contributor
Posts: 18

Re: Proc export to Excel now loses currency format

Posted in reply to data_null__

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!

PROC Star
Posts: 7,491

Re: Proc export to Excel now loses currency format

Posted in reply to JSWilson64

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

Occasional Contributor
Posts: 18

Re: Proc export to Excel now loses currency format

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 902 views
  • 3 likes
  • 5 in conversation