topic Export 2 million records to Excel File !! in SAS Programming

Export 2 million records to Excel File !!

Rhino84 — Tue, 07 Dec 2021 20:24:14 GMT

I need SAS code to successfully export 2 million records (2 million rows X 12 columns ) in Excel file

Re: Export 2 million records to Excel File !!

Reeza — Tue, 07 Dec 2021 20:31:19 GMT

https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

@Rhino84 wrote:

I need SAS code to successfully export 2 million records (2 million rows X 12 columns ) in Excel file

Re: Export 2 million records to Excel File !!

Rhino84 — Tue, 07 Dec 2021 20:32:59 GMT

I can use CSV though ?

Re: Export 2 million records to Excel File !!

ballardw — Tue, 07 Dec 2021 20:36:22 GMT

@Rhino84 wrote:
I can use CSV though ?

How will you use the CSV?

Re: Export 2 million records to Excel File !!

Reeza — Tue, 07 Dec 2021 20:39:21 GMT

CSV is not Excel, it's a text file. Are you updating your question to ask how to export your data to a CSV file?

If so, see the tutorial here:
https://stats.idre.ucla.edu/sas/faq/how-do-i-write-out-a-file-that-uses-commas-tabs-or-spaces-as-delimiters-to-separate-variables-in-sas/

Excel will not be able to load a file of 2 million rows from a CSV.

Re: Export 2 million records to Excel File !!

ChrisHemedinger — Tue, 07 Dec 2021 20:40:08 GMT

As Reeza points out, Excel can hold only about 1 million rows per sheet. You could export with multiple passes into multiple sheets. Something like:

filename out "c:\project\bigexcel.xlsx"; proc export data=bigfile(obs=1000000) dbms=xlsx outfile=out replace; sheet="First million"; run; proc export data=bigfile(fistobs=1000001) dbms=xlsx outfile=out replace; sheet="Second million"; run;

But...this is probably a terrible use for Excel and data storage. But sometimes that's what our clients ask for...

Re: Export 2 million records to Excel File !!

SASKiwi — Tue, 07 Dec 2021 20:40:13 GMT

Yes, you can use PROC EXPORT to create a CSV file. You just won't be able to open it in Excel.

Re: Export 2 million records to Excel File !!

Rhino84 — Tue, 07 Dec 2021 20:49:49 GMT

What i meanst was, i ould export my file from SAS to a csv spreadsheet too

Re: Export 2 million records to Excel File !!

Reeza — Tue, 07 Dec 2021 20:53:37 GMT

CSV is not a spreadsheet. It's a comma separate text file, that Excel happens to be able to read and interpret and is typically the default viewer. But you don't have formats, cells, borders or multiple sheets. Just raw data.