Proc export SAS dataset to CSV file

[cho16]

Hi

I need to export the SAS dataset that has more than 1 million records (1,120,897) and 35 columns to the .CSV file.

But while exporting the dataset , all the rows are not exported.

Could you please let me know how can i export all the rows to the .CSV file ?

proc export data=NEW dbms=xlsx

outfile="\\HFNT63\devl\web\NSLIJ_testing\NEW_ENROLLMENT.xlsx"

replace;

run;

Thanks

[ballardw]

Your file name says you are NOT exporting to CSV but XLSX.

And Excel has file size limitations that CSV does not.

[cho16]

Sorry i am trying to export the file to CSV file

proc export data=NEW_ENROLLMENT dbms=xlsx

outfile="\\HFNT63\devl\web\NSLIJ_testing\NEW_ENROLLMENT.csv" dbms=csv

replace;

run;

[Reeza]

Review your code. You still have DBMS= XLSX in there. Remove that portion. Note that you will need to open the file in a text editor not excel to verify the number of records. 

[cho16]

Thanks Reeza !

But how to export more than 2 million of records by using proc export.

The query succeds in the window but when i try to open the file it shows the "file was not load completely".

Could you please let me know how to resolve this ?

[ballardw]

DO NOT OPEN the file with Excel. EXCEL has many limitations and behaviors that are not suited for large datasets.

Whatever you may be thinking of doing in Excel, you are likley better off doing in SAS and then exporting as no one in their right mind scrolls through two million lines of anything.

From:  https://support.office.com/en-us/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c...

Excel 2010, 2013, 2016

Worksheet size

1,048,576 rows by 16,384 columns

So you cannot have many more than 1 million rows when opening a file in Excel.

[Oligolas · Accepted solution]

Why are you using the xlsx DBMS if you want to create a CSV File !?

Forget your code and use the SAS utility macro (already included in SAS)

%DS2CSV Macro

Example:

%ds2csv (
   data=new, 
   runmode=b, 
   csvfile=\\HFNT63\devl\web\NSLIJ_testing\NEW_ENROLLMENT.csv
 );

From @ballardw

DO NOT OPEN the file with Excel. EXCEL has many limitations and behaviors that are not suited for large datasets.

Whatever you may be thinking of doing in Excel, you are likley better off doing in SAS and then exporting as no one in their right mind scrolls through two million lines of anything.

From:  https://support.office.com/en-us/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c...

Excel 2010, 2013, 2016

Worksheet size

1,048,576 rows by 16,384 columns

So you cannot have many more than 1 million rows when opening a file in Excel.

---

[jsphnwllms]

proc export data=NEW dbms=csv

outfile="\\HFNT63\devl\web\NSLIJ_testing\NEW_ENROLLMENT.csv"

replace;

run;

Do not open the file with Excel. You may import the csv file in ACCESS, but there is a 2GB size limit in ACCESS.