PROC EXPORT conditionning number of rows

[fabdu92]

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?

Thanks 

[RW9]

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;'); 
  end;
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.

[Reeza]

You want functionality like this

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#p011imau3tm4jen1u...

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