Excel format compability issue in SAS EG 5.1

Reply
Contributor
Posts: 51

Excel format compability issue in SAS EG 5.1

I have a problem of Excel format on my organization. We use SAS EG 5.1 server.

Our programs generate Excel output in the format of XLSX.

Recently, the System administrator moved the storage from Unix to windows platform.

We can not open any Excel format XLSX any more (for new output ONLY), but the old excel files still be opened and read.

I check Microsoft Office in my local computer. It is MS version 2010.

I can not find the problem. If MS Excel 2010 can not open the new XLSX files, why it can open the old ones.

Our company does not allow install the add-in driver to current MS software.

Can you give me a hint to solve this issue.

Thanks in advance.

-------------------------------

William

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Excel format compability issue in SAS EG 5.1

Could you clarify what you mean by "We can not open any Excel format XLSX any more".  You might also try opening an old XLSX and a new one in Notepad or some similar app to check they look similar.  Maybe ExcelXp tagset is missing from new install so output is not created correctly.  Maybe post a small example if your able.

Contributor
Posts: 51

Re: Excel format compability issue in SAS EG 5.1

RW9

My problem is that SAS program ran successfully to create Excel file (XLSX format). I can see the file and when I click to open it, it runs for a while (look as if it works) and a pop up  screen that says current excel software  is not compatible to open.

This problem is not happen to existing excel files (XLSX format, created before the moving storage platform from Linux to Windows)

I try to switch the SAS source code to create XLS format, but the limitation of about 65,000 rows for a worksheet bring me a lot of trouble. My SAS data set has the average of  5 to 10 millions of records.

My company uses SAS EG 5.1 (64 bits) and Microsoft Office (on local computer) version 2010.

Hope you bring me a direction to solve this issue.

Thanks for your kindly suggestion.

--------------------------------------------

William

Regular Learner
Posts: 1

Re: Excel format compability issue in SAS EG 5.1

Hi.

I hope you might done this. If not please perform steps.

Please disable Compatibility check option in Microsoft Excel 2010.

Click File tab -> click Info -> click Check for issues -> Click Check compatibility -> Clear Check compatibility when saving this workbook.

Hope this resolves your issue.

Esteemed Advisor
Posts: 6,646

Re: Excel format compability issue in SAS EG 5.1

Excel has a limit of 1M rows:

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx

If you say your SAS dataset has 5 to 10 M records, there you have your problem.

Do as much as you can in SAS (to reduce the size of the data) and only use Excel for presentation, Excel is about as good for business intelligence as a blunt stick to the eye is good for improving your vision.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 1,098

Re: Excel format compability issue in SAS EG 5.1

True, Excel has its own limitations. I didn't know that is 1M of observations, so great contribution Kurt. Thanks also by my side.

Super User
Posts: 1,098

Re: Excel format compability issue in SAS EG 5.1

I think that more details about the exact error that you can not open your XLSX files, it would be helpful to understand a little better what is happening.

In the meantime, please check this SAS note, maybe it can help you.

40383 - An unreadable content error occurs when you open Microsoft Excel files that were created by ...

Also, it could be interesting to know:

- how your programs are generating the Excel output.

- if only the storage or also the SAS server was migrated.

- If the storage is/was 32-bit or 64-bit.

- other possible changes.

Valued Guide
Posts: 3,206

Re: Excel format compability issue in SAS EG 5.1

The old xls format should be avoided as it relies on mdac jet ACE drivers in a dedicated format.

Microsoft changed that in 2007. The format is very alike the well documented open office format (OASIS) as MS did a lot of work on that.

The real xlsx format is essential a zip file file containing XML files.   There is no limit in this structure other than the the zip limitation and the xml ones.

The excel limits have moved to internal ones of the Excel product.

There should be no problem when all is done conforming these standards. There must be a loophole somewhere.

There can be something with local/intranet/intrnet. Every file in Windows is coming from one of those zones.

When the file is recognized coming from a foreign zone often all update processing macro processing is blocked.This could be caused by moving some storage approach. When the technical approach is a SAMBA connection presenting you the data in a Windows drive where it is really Unix. The kind of effect you described could happen.

With this there could also be some wrong translation where files are getting damaged by not offering them in binary exchange mode.

You did not mention anything on changing the SAS servers from Unix to Windows.

Sometimes the real association is faked. With SAS ODS tagsets a XML file is generated not a xls or xlslx file. The salesman is selling this faked type as the real thing.

     

Please verify:

- the real type of the file being delivered

- the SAS version and OS type (visible with %put _all_  )

- the new moved storage that has used (in the Win-domain or external)

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 7 replies
  • 275 views
  • 8 likes
  • 6 in conversation