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
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)
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.
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.
- Cheers -
Your file name says you are NOT exporting to CSV but XLSX.
And Excel has file size limitations that CSV does not.
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;
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.
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 ?
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.
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.
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)
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.
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.
- Cheers -
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.