SAS 9.2 64bit, DBMS=XLS export - can't open file in Office2007 (Windows7)

Reply
Occasional Contributor
Posts: 9

SAS 9.2 64bit, DBMS=XLS export - can't open file in Office2007 (Windows7)

Hello all,

We have upgraded to 64bit SAS 9.2 and have encountered a new problem.  The excel files created by proc export using DBMS=XLS creates .xls files that will not open on Windows7 machines running Office2007.  SAS log does not contain an error for the proc export step.  When trying to open, either excel stalls and has to be forced to close, or an error message appears stating the file is corrupt.  These files open fine on WindowsXP machines with Office2003.

Here are some details:

Windows Server 2008 R2 Standard, SP1

64-bit Operating System

SAS 9.2 TS Level2M3

X64_SRV08 platform

Any ideas on fixing this?  I searched and could not find anything, nor any alternatives that could be used.  (I tried DBMS=EXCELCS, but it did not work - SAS error and did not export a file)

Thanks very much,

Anne

Respected Advisor
Posts: 4,173

Re: SAS 9.2 64bit, DBMS=XLS export - can't open file in Office2007 (Windows7)

Posted in reply to AnneOKeefe

Not a solution - just a bit of input:

DBMS=EXCELCS needs the PC File Server licensed in order to work.

http://support.sas.com/kb/33/228.html

DBMS=XLS creates according to doc an Excel2003 compatible format.

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003102702.htm

Still: I don't understand why you shouldn't be able to open this under Win7 with a newer Excel version as MS Excel itself seems to be quite good in dealing with older versions.

I found this very helpful blog: How do I export from SAS to Excel files: Let me count the ways | PROC-X.com

Suggest you open a track with SAS Tech Support and keep us posted here what the cause and resolution for this issue is.

Occasional Contributor
Posts: 9

Re: SAS 9.2 64bit, DBMS=XLS export - can't open file in Office2007 (Windows7)

Thank you for your suggestions.  I am trying to find out if PC File Server can be installed.  I am also trying to find out if Office 2007 needs some security settings changed in order to read an Office 2003 file.

In the meantime, I have submitted a ticket to SAS support and will update if they can resolve this.

Again, many thanks.

Anne

SAS Employee
Posts: 6

Re: SAS 9.2 64bit, DBMS=XLS export - can't open file in Office2007 (Windows7)

Posted in reply to AnneOKeefe

HI

I fighting with same problem and found a suggestion to use version option in export command. It did not helped me, but I guess, that it is worth of try, because in some cases it helped. (BTW! this excel, which do not open in Office 2010,  opens easily with older version of Office - in my case OfficeXP).

Version syntax:

export data=library.dataset

     outfile="C:\oleglib\test" dbms=XLS replace;

     VERSION=2003;

run;

/oleg

Contributor
Posts: 52

Re: SAS 9.2 64bit, DBMS=XLS export - can't open file in Office2007 (Windows7)

Hi, I have the same problem and version=2003 did not help. I'm running windows 7.

Any news?

New Contributor
Posts: 2

Re: SAS 9.2 64bit, DBMS=XLS export - can't open file in Office2007 (Windows7)

Posted in reply to AnneOKeefe

The bug is known to SAS. http://support.sas.com/kb/40/383.html

It says if you are using Excel 2007 version 12.0.4518 or earlier, you can add the statement VERSION=2003; to the proc export.

If you're using a later version of 2007 or using 2010, apparently you're out of luck.

"If you have a version of Excel 2007 later than 12.0.4518, or if you are using Excel 2010, then you must do one of the following:

  • Open the Excel file in Excel 2003 and resave the file.
  • Use the EXCELCS driver with the SAS® PC Files Server.
  • Export the file to a different format such as a .csv or a tab-delimited .txt file."

That's not much help, because

  • after we upgraded everybody we don't have an older version of Excel around,
  • we don't have SAS® PC Files Server

  • we're outputting .xls in the first place  because the ultimate goal spreadsheet has like 50 tabs, and the thought of assembling it each day is unpleasant, and they vary enough from day to day that a master template to link to 50 different csv files isn't workable.

The official fix is install version 9.3 but we haven't got there yet. I'm a little bit alarmed because it says

  • It can export only one sheet per file.
  • The entire XLSX file is replaced.

There's a reference to what appears to be the same bug, but in SAS® Customer Intelligence Studio. http://support.sas.com/kb/44/754.html

"To work around this problem, use the .CSV export type instead of Microsoft Excel in SAS Customer Intelligence Studio or open the Microsoft Excel file in an older version of Microsoft Excel."

Ask a Question
Discussion stats
  • 5 replies
  • 5595 views
  • 3 likes
  • 5 in conversation