have proc export determine if record count is too large for output file before attempting export

[paulkaefer]

If I export a dataset with more than about one million records to Excel, it won't work because that is too much for Excel 2010 to handle.

Can proc export determine this before doing the export, rather than processing the export and after some time

breaking, with the error ERROR: Too many records for the output file?

This would save time if a dataset that is too large is exported, and save space - for the current process I'm building, I now see a dataset_name.$$1 file that's 120 MB.

Another suggestion is to make this a warning, or have an option to change this from an error to a warning.

[RW9]

Can you note check it before starting the export?

data _null_;
  set sashelp.vtable (where=(libname="<yourlib>" and memname="<yourds>"));
  if nobs <= 1000000 then call execute("proc export datafile=...");
run;

More to the point however, Excel is not a good data file format (which you are experiencing here).  Choose an appropriate file format for the task in hand.  CSV, XML, Json, for example have none of these restrictions, and are open to be read by any software.  Far more appropriate.

[Reeza]

My guess would be it would be hard to implement because Excel can change that at some point and then it could take a bit for SAS to update that feature. It could be an option perhaps? Or maybe a custom macro that did it, that wouldn't be too hard to implement at all as a workaround. 

[paulkaefer]

Though @RW9 is right, and it would be nice to use something like CSV that is much more universal, unfortunately it's not always as simple as not using Excel. Especially if users rely on the report, are comfortable with Excel, have processes that require Excel, etc.

The simple code sample is good, and as @Reeza suggests, it could easily be macro-ified.

I don't like the argument "it could take a bit for SAS..." because if there's a new version/spec for Excel files, SAS will have to adapt anyway. Is there some way that SAS could detect a version of Excel and/or how many rows are allowed?

[Reeza]

@paulkaefer implementation is ultimately up to SAS and they probably could just add an option that would allow a user to check. Or change the DBMS specifications such that XLSX allowed 1million, XLSX2 allows unlimited etc. There are options and it's a fair approach. But as requests go tbh this would be pretty low on my list to implement. I'd rather see a way to specify types in an Excel file first.