topic Re: PROC EXPORT conditionning number of rows in SAS Programming

PROC EXPORT conditionning number of rows

fabdu92 — Wed, 23 Nov 2016 10:30:03 GMT

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

Re: PROC EXPORT conditionning number of rows

RW9 — Wed, 23 Nov 2016 10:47:59 GMT

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.

Re: PROC EXPORT conditionning number of rows

Reeza — Wed, 23 Nov 2016 10:50:19 GMT

You want functionality like this

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

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