DATA Step, Macro, Functions and more

Proc export to CSV

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Proc export to CSV

Hi

 

I need to export the SAS dataset that has more than 1 million records (1120897) 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


Accepted Solutions
Solution
‎03-30-2017 07:55 AM
Frequent Contributor
Posts: 103

Re: Proc export to CSV

[ Edited ]

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.



 

________________________

- That still only counts as one -

View solution in original post


All Replies
Super User
Posts: 10,476

Re: Proc export to CSV

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

And Excel has file size limitations that CSV does not.

Contributor
Posts: 30

Re: Proc export to CSV

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;

Super User
Posts: 17,775

Re: Proc export to CSV

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. 

Contributor
Posts: 30

Re: Proc export to CSV

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 ?

Super User
Posts: 10,476

Re: Proc export to CSV

[ Edited ]

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.

 

Solution
‎03-30-2017 07:55 AM
Frequent Contributor
Posts: 103

Re: Proc export to CSV

[ Edited ]

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.



 

________________________

- That still only counts as one -

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 14707 views
  • 2 likes
  • 4 in conversation