topic Re: sas eg export limitations in SAS Enterprise Guide

sas eg export limitations

sas_9 — Fri, 27 Sep 2013 18:05:38 GMT

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!

Re: sas eg export limitations

jakarman — Fri, 27 Sep 2013 18:23:14 GMT

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?

Re: sas eg export limitations

sas_9 — Fri, 27 Sep 2013 18:46:21 GMT

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!

Re: sas eg export limitations

jakarman — Fri, 27 Sep 2013 19:52:29 GMT

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.

Re: sas eg export limitations

sas_9 — Fri, 27 Sep 2013 20:36:27 GMT

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

Thank!

Re: sas eg export limitations

sas_9 — Fri, 27 Sep 2013 20:50:53 GMT

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

Re: sas eg export limitations

jakarman — Sat, 28 Sep 2013 08:14:11 GMT

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