11-23-2016 05:30 AM
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?
11-23-2016 05:47 AM
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.
11-23-2016 05:50 AM
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.