BookmarkSubscribeRSS Feed
sas_9
Obsidian | Level 7

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!

6 REPLIES 6
jakarman
Barite | Level 11

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? 

---->-- ja karman --<-----
sas_9
Obsidian | Level 7

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
Barite | Level 11

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.

---->-- ja karman --<-----
sas_9
Obsidian | Level 7

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

Thank!

sas_9
Obsidian | Level 7

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
Barite | Level 11

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 

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3344 views
  • 0 likes
  • 2 in conversation