DATA Step, Macro, Functions and more

PROC EXPORT conditionning number of rows

Posts: 45

PROC EXPORT conditionning number of rows

Hi all,


As you know, Excel is limited by its number of rows. According to this, I have a user requirement telling that: "The maximum number of records is 1.048,576 rows"


I have to export a table in an Excel sheet with famous PROC EXPORT


I want to perform this requirement by exporting the table in the Excel sheet if the number of rows is less, or else displaying a error in the Excel sheet telling something like "Too much data: the number of records is N", and replace N by the number.


But honestly, I am not an expert in SAS, even more a beginner than expert so I really don't know if there is a way to do it.

Can you help me?



Super User
Super User
Posts: 9,790

Re: PROC EXPORT conditionning number of rows

Well, first off is the usual text.  Why are you using Excel - unstrcutured, uncontrolled, very bad format for data transport - as a data transport format?  Not only will you hit certain restrictions on size with it, Excel has lots of "features" which really make it a bad choice.  Also its far more difficult to import/qc.  I really would use any other available format - CSV, XML, dataset, delimited, json etc.  All are far better.


As for your code you could do (although if your not familiar with SAS, this is a bit more advanced):

data _null_;
  set sashelp.vtable (where=(libname="L1" and memname="D1"));
  do i=1 to (nobs / 1000);
    call execute('data tmp; set l1.d1; if '||strip(put(i*1000,best.))||' <=_n_ < '||strip(put((i+1)*1000),best.))||' then output; run;');
    call execute('proc export outfile="c:\outputfile'||strip(put(i,best.))||'.xlsx" data=tmp; run;'); 

What this does is generate a datastep and proc export for each block of observations of 1000 obs.  Note change L1 and D1 to your lib and dataset, and change 1000 to number of obs.

However I still really recommned not using Excel.

Super User
Posts: 23,928

Re: PROC EXPORT conditionning number of rows

You want functionality like this


in this case of 0, it prints an error message. You can change the proc print to an export and the message as required. 

Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation