sas eg export limitations

[sas_9]

Hi Guys -  if someone can please tell me what is the limits (no. of the records) of sas eg on ROWS and COLUMNS to export/import from/withing excel (.xls or .xlsx) if i am using sas eg 4.1, 4.2, 4.3 and 5.1...

Thanks!

[jakarman]

Sandy,

It is not just the SAS or Eguide that is playing a role. Also:

- 32-64 office/Excel and/or SAS.

- Used devicedrivers (possible hidden)

Is this just an informative question or a real life issue to solve? 

[sas_9]

hey this is only for my information and lets assume that i am using sas eg 4.1 and upgrading to 5.1 on windows 32 bit with excel 2007 and 2010...

Thanks!

[jakarman]

With 32-bit systems you need to look at the 2Gb-limit. It will be possible lower.

Older Excel 2007 file types are limited to the 64k Limit on number on rows and 256 colums. Hmm 2010 changed that. See:

SAS/ACCESS(R) 9.4 Interface to PC Files: Reference (Microsoft Excel Workbook Files) it has a chapter on them

The ACE en Jet engine are mentioned to have the limitation of 255 cols.

So don't use Jet/ACE  that is the xlsx format as essential being a XML file. That is changed with eguide 5.1. The addin can be propagated 4.3   

http://support.sas.com/resources/papers/proceedings13/415-2013.pdf

Confusing isn't it.

[sas_9]

Jaap - your writing is helpful but still i am wonder if i can get some moer detail......

Thank!

[sas_9]

i have found this...so far...but not sure if eg handle same as well...

Remember the following points as you work with Microsoft Excel files.              

• Excel 4 files allow only one spreadsheet per file.                             
• Excel 4, Excel 5, and Excel 95 limits are 256 columns and 16,384 rows.                             
• Excel 97, 2000, 2002, 2003 limits are 256 columns and 65,536 rows.                             
• Excel 2007 limits are 16,384 columns and 1,048,576 rows.                             
• Excel 95 files are treated as the same format as Excel 5 files.                             
• Excel 2000, 2002, and 2003 files with an .xls file extension are treated as the same format as Excel 97 files.                             

Excel 2007 and 2010 have three different file extensions:                             

• .xlsb                                         
• .xlsm                                         
• .xlsx

[jakarman]

I am looking to your question. Im a little bit different thinking to find the information.

It is the file-type supplier/standard that is:

- Microsoft / Excel,    eg at: Excel specifications and limits - Excel - Office.com

- Microsoft XLS file-type  ACE/JET.

  Data Source Information Properties (OLE DB Jet Provider) it is de DBPROP_MAXINDEXSIZE being limited to 255

  All office interfaces (Access / Word) are mentinoning this column limit with no adjusment.

  The reserved Words: List of Microsoft Jet 4.0 reserved words

- OASIS ODF v1.2 for those XLSX types    entry at Standards | OASIS   (opendocument V1.2)

  In this specification you find no limits. It are many files in a zip-file. The contents are XML-files. Yes this is about XLSX Excel interface. 

Recap:
- Excel has his own limits while running. This is version dependant.  (MS)
- The default interfaces as of ACE/JET are limiting to 255 columns. (MS)
- The old XLS file-types have limations of the old Excel versions. (MS)

- The new XLSX file-type has no fixed limitation.  (Zipped file with xml files)   (Oasis)

The one that is most restrictive at a moment is the one that counts