DOES SAS 9.3 Supports Excel2010

[Kiran]

From SAS when we run the proc export procedure to export the data into MS-Excel 2010 version i.,e XLSX extension the spreadsheet created successfully. No error in the SAS log
When I try to open the spread sheet its throwing me the pop-up like ‘’ "Excel Found Unredable Contents '..........xlsx' do you want to recover the contents of this workbook? If you trust the source of this work book click 'yes'”

Even though if u click yes the data will not be there in the spreadsheet.
SAS version is 9.3
SAS Procedure used to export the data:
Proc export dbms=xlsx
Data=a outfile ‘ b.xlsx’ replace
Sheet=’’All’’;
Run;

Could you please anyone suggest me how can i fix this...?

[art297]

You posted this twice.  try dbms=excel

[Cynthia_sas]

Also, I'm not sure why you are posting and/or reposting this in the SAS/GRAPH and ODS Graphics forum. This would be better posted in the SAS Procedures forum. When you have PROC EXPORT questions, you cannot export anything remotely involved with graphics using PROC EXPORT.

In addition, there are several Tech Support notes about using PROC EXPORT -- in SAS 9.3 and the various possibilities for the DBMS= option. A search of support.sas.com should reveal the notes to you.

cynthia

[GeoffreyBrent]

I encountered a similar problem when migrating from SAS 9.1 to 9.3. In our case, I think the cause was that our SAS 9.3 server had mistakenly been set to use the 32-bit version of Microsoft Access; once it was reconfigured to the 64-bit version, things worked fine. (Can't give you any more detail on how to do that, our tech support guys handled the detail.)

[Bago]

Hey Kiran,

The problem will occur when you export a large number of observations (+65k ) to the xlsx file e.g.

data largevars;

do obsno=1 to 70000;
length x1-x30 $ 50;
array x $ x1-x30;
do colno=1 to 15;
x(colno) = 'This is rownum '|| obsno;
end;
do colno=16 to 30;
x(colno) = 'This are more columns '|| obsno;
end;
output; end; run;

proc export data=largevars outfile='c:\temp\mybig.xlsx' dbms=XLSX;
sheet='mysheet';
run;

The problem will occur when you do the export using SAS 9.3M1. SAS fixed this problem in SAS 9.3M2. Therefore if you have access to SAS 9.3M2 i would recommend to install this version.

Kind regards,

Bart

[Julitski]

Hi all,

Yes SAS 9.3 English (Local SAS) supports Excel 2010.  However, please note of the following.

SAS 9.3 -> Windows Excel 2010 (64Bits) -- 100% compatible.
SAS 9.3 -> Windows Excel 2010 (32Bits) -- NOT 100% compatible. There are known issues which until now not yet permanently fixed.

Needless to say, the fix is to upgrade Excel to 64 Bits.

Most common errors which I myself have encountered using SAS 9.3 w/ Excel 32Bits are:

1. Error exporting to old excel formats (i.e. Excel 95/97) - No fix

2. Error exporting/overwriting an existing Microsoft Excel (.XLSX)  worksheet which has a link to another worksheet. Will get corruption error, - No fix

3. Successful exporting to Microsoft Excel (.XLSX)  worksheet (no error in sas log),  but getting corruption error when opening (as reported in this thread.)

For no 3, I have done some simulations and found one cause of this problem (at least for my case) is because of a Column w/ very long length, i.e length > 37 characters.

Solution 1.- removes the column (it worked).

Solution 2. - shortened the length of the column, from 100 -> 90, still failed,  shortened to 80, still failed. And the magic number is 37

Solution 2 is inconclusive, as this error may be a combination of total number of columns and length of columns, because if you try to create a sas dataset w/ 1 column and length = 100 characters, the export works.

[Julitski]

Hi all,

For Error 2. Error exporting/overwriting an existing Microsoft Excel (.XLSX)  worksheet which has a link to another worksheet. Will get corruption error, - No fix

- Even there is no link, there is also corruption and this is mainly on dates columns - Still no known fix.

[jakarman]

There are errors related to the Microsoft ace driver.

As sas is dependent on that it cannot fix that errors remember it is ms.

SAS could better use the xlsx open office specification oasis.org and with that the ms extensions.

If you would classify that decision not doing so as a sas problem. Your opinion is right to say.

In that case it is less a technical issue more a political one.