Excel File Size Limit

[OS2Rules]

Hi All:

Just a quick question that someone asked me that I couldn't answer...

We are running SAS 9.1.3 and use Office 2003.

I understand about the 64k record limit imposed by Office - but - what if I write the file using the EXCELXP tagset?  Since the output from this is actually in XML, shouldn't I be able to write as many records as I want?  I know I wouldn't be able to open the file in Office 2003, but what about Office 2007 (or 2010 for that matter)?

Also - if I use PROC EXPORT to export the data to Excel, is the limit applied?  If so, is the limit a restriction of SAS, or a restiction of the Office version?

The backgound is that we are trying to create a file with >64k records in it that we sent to an outside client.  They insist that the file is in Excel format (not a csv for example), and our process is failing during the export.  I don't know which version of Office they are running, but if I assume that it is more recent that ours, how do I get the data to them in a format they can read?

[Cynthia_sas]

Hi:

  I only have Excel 2010 to test on, but this worked for me to create 76,000 rows and when I opened the XML file with Excel 2010, I was able to see all 76,000 rows in one worksheet.

  I do not know what will happen in earlier versions of Excel.

cynthia

data makebig;

  length newname $16;

  set sashelp.class;

  do i = 1 to 4000;

    newname = catx('~',name,put(i,z4.));

    output;

  end;

run;

ods tagsets.excelxp file='c:\temp\trybig.xml'

    style=sasweb;

proc print data=makebig noobs;

  var newname age sex height weight;

run;

ods tagsets.excelxp close;

[SASKiwi]

We have the same issue as we use SAS 9.1.3 and Office 2003. We bypassed the problem by PROC EXPORTing to a CSV and using Excel 2007 to read the CSV. You can then open the CSV in Excel 2007 and save it as a 'real' Excel 2007 spreadsheet which has an xlsx extension. You could also do what Cynthia has done, but bear in mind XML spreadsheets are much larger than XLS ones. Also I don't see any problem with providing the customer with a CSV as long as there is no special formatting applied. PROC EXPORT using XLS and CSV will give exactly the same result, as long as there are no embedded commas in the data.

[Tom]

Looks likes your limitation is that you only have Excel 2003. That is limited to 64K rows.