topic Re: DOES SAS 9.3 Supports Excel2010 in Graphics Programming

DOES SAS 9.3 Supports Excel2010

Kiran — Fri, 17 Aug 2012 10:38:50 GMT

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...?

Re: DOES SAS 9.3 Supports Excel2010

art297 — Fri, 17 Aug 2012 12:28:06 GMT

You posted this twice. try dbms=excel

Re: DOES SAS 9.3 Supports Excel2010

Cynthia_sas — Tue, 21 Aug 2012 01:01:01 GMT

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

Re: DOES SAS 9.3 Supports Excel2010

GeoffreyBrent — Tue, 21 Aug 2012 03:42:16 GMT

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.)

Re: DOES SAS 9.3 Supports Excel2010

Bago — Wed, 06 Feb 2013 08:52:34 GMT

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

Re: DOES SAS 9.3 Supports Excel2010

Julitski — Tue, 14 Oct 2014 02:44:21 GMT

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.

Re: DOES SAS 9.3 Supports Excel2010

Julitski — Tue, 14 Oct 2014 03:12:13 GMT

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.

Re: DOES SAS 9.3 Supports Excel2010

jakarman — Tue, 14 Oct 2014 07:16:54 GMT

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.