topic Re: Proc export to CSV in SAS Programming

Proc export SAS dataset to CSV file

cho16 — Mon, 07 Jun 2021 03:28:49 GMT

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

Re: Proc export to CSV

ballardw — Mon, 26 Sep 2016 16:29:06 GMT

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

And Excel has file size limitations that CSV does not.

Re: Proc export to CSV

cho16 — Mon, 26 Sep 2016 16:53:20 GMT

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;

Re: Proc export to CSV

Reeza — Mon, 26 Sep 2016 17:35:53 GMT

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.

Re: Proc export to CSV

cho16 — Mon, 26 Sep 2016 20:36:08 GMT

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 ?

Re: Proc export to CSV

ballardw — Mon, 26 Sep 2016 22:05:21 GMT

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-67c00b05040f

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.

Re: Proc export to CSV

Oligolas — Thu, 30 Mar 2017 11:55:34 GMT

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.

Re: Proc export to CSV

jsphnwllms — Tue, 03 Oct 2017 18:43:01 GMT

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.