Export 2 million records to Excel File !!

[Rhino84]

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

[Reeza]

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

---

@Rhino84 wrote:

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

---

[Rhino84]

I can use CSV though ?

[ballardw]

---

@Rhino84 wrote:
I can use CSV though ?

---

How will you use the CSV?

[Reeza]

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-del...

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

[Rhino84]

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

[Reeza]

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.

[SASKiwi]

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

[ChrisHemedinger]

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...