How to export millions of rows to excel in SAS

[inquistive]

Hi Experts,

I have millions of rows in my dataset. xlsx has a limit of 1048576 rows per sheet. I have 262059590 rows in a SAS dataset. How can I distribute the rows in the dataset and send them to multiple sheets (10,48,576 rows per sheet) at once ? 

Thanks

[PaigeMiller]

A quick trip to Google informs me that Excel can have a maximum of around 1,000,000 rows — that's not the exact number but the point is you cannot export "millions" of rows to Excel. 

You will have to chop your data into portions that are less than the max number of rows, and then export each portion.

[AMSAS]

Why would you want to export millions of rows to Excel? What's the final objective as I'm guessing there's probably a better solution

[inquistive]

@ AMSAS,
I completely understand your point here. The issue here is pertinent managers want historical records to analyze on their own.

[Reeza]

The file will be too big to work with in Excel due to the amount of records. Export it as CSV and connect to it via Excel using a data connection/dsn connection instead or connect to the CSV via Power Query.

[ballardw]

---

@inquistive wrote:
@ AMSAS,
I completely understand your point here. The issue here is pertinent managers want historical records to analyze on their own.

---

I would ask these "managers" exactly how they expect to "analyze on their own". What type of analysis?

Records, that is another story, but the "record" has to be in a usable form and hundreds of sheets in Excel is not very workable.

No human is going to examine that much, dare I say garbage, by eye.

<anecdote mode>

Years ago when Excel was limited to 65K records in a sheet I worked with an organization that had interns. One of them asked why his attempt at some analysis was taking so long (4 hours and not complete). I briefly explained the overhead in spreadsheet cells for calculation and the likelihood he was stretching his PC memory. Then pointed out that of the 250K records he had exported from our SAS data only 65K were being used.

I asked what he was trying to find out from the data. Showed him the Proc Means code to achieve than and the 2.5 seconds it took to execute. I suggested that "proper tool for the task" is a good idea.

<anecdote mode/off>

[PaigeMiller]

---

@inquistive wrote:
@ AMSAS,
I completely understand your point here. The issue here is pertinent managers want historical records to analyze on their own.

---

How would managers analyze millions of records in many many Excel tabs? What possible analysis could they do, without SAS or similar programming language? It would be nearly impossible to do anything meaningful in Excel.

[Reeza · Accepted solution]

If your numbers are correct you have 262,059,590 records and Excels limit is 1,048,576 per sheet (-1 for header row)

=> 250 worksheets

I think there may be a limit of 255 worksheets created this way, but not sure. 

This will be a very unwieldy/unworkable Excel file IMO, not to mention a large file.

Update the filepath below and test that it works first, then try it on your data set. I suspect you're going to get a large file and this would take a while to write that many records to an Excel file. 

%macro export_split (dsn=, size=);
    %*Get number of records and calculate the number of files needed;
    data _null_;
        set &dsn. nobs=_nobs;
        call symputx('nrecs', _nobs);
        n_files=ceil(_nobs/ &size.);
        call symputx('nfiles', n_files);
        stop;
    run;

    %*Set the start and end of data set to get first data set;
    %let first=1;
    %let last=&size.;
    
    %*Loop to split files;
    %do i=1 %to &nfiles;
    
        %*Split file by number of records;
        proc export data= &dsn. (firstobs=&first obs=&last) outfile='/home/fkhurshed/Demo1.xlsx' dbms=xlsx; Sheet="Page&i.";
        run;

        %*Increment counters to have correct first/last;
        %let first = %eval(&last+1);
        %let last = %eval((&i. + 1)*&size.);
    %end;
%mend export;

*Example call;
*After running this, you should find 9 data sets named Split1-Split9;
%export_split(dsn=sashelp.cars, size=50);

---

@inquistive wrote:

Hi Experts,

I have millions of rows in my dataset. xlsx has a limit of 1048576 rows per sheet. I have 262059590 rows in a SAS dataset. How can I distribute the rows in the dataset and send them to multiple sheets (10,48,576 rows per sheet) at once ? 

Thanks

---

[inquistive]

Thank you, Reeza!
So kind of you to share the code!