Hi,
I want to export Large dataset(upto 1 million records) into CSV file directly instead of saving into local system, but exporting CSV file is taking around 30 mins time to extract into CSV file itself.
*ProcessBody;
ods listing close;
data _null_;
rc= stpsrv_header('Content-type', 'application/vnd.ms-excel');
rc= stpsrv_header('Content-disposition', 'attachment;
filename= Report1.csv');
run;
%let _ODSDEST=TAGSETS.csvall;
%stpbegin;
ods tagsets.csvall options(sheet_name="Report1");
Proc Print data=Report1 noobs width=full style(data)={background=white};
format COL1 COL2 yymmd9.;
var COL1 COL2 COL3 COL4;
run;
ods tagsets.csvall close;
%stpend;
Anyway to reduce the exporting time. Your help would be appreciated.
I suspect we are missing part of the puzzle since you have the 'Content type' stuff. Which means you're wanting something besides plain vanilla CSV.
What do you mean by "directly"?
Also, even if you can use tagstes/ods for this stuff, my gut feeling is that there might be more efficient and equally easy/easier ways, like PROC EXPORT or a simple data step.
What is the purpose of this export?
I'm guessing you're running this through WRS?
Then its probably a bit of network issue as well.
Can you create the file and then ftp it somewhere?
I am running it thru stored process, I want to print SAS dataset to CSV file. I assume Proc export will work only by saving the file, but i dont want to use ftp. I am using plain csv file format to print the sas dataset for 1 million records. Can u suggest me an efficient way to extract into CSV file
You have closed the Listing destination but is the HTML destination also open? That will eat a lot of time building an HTML table.
Hi ballardw,
I am closing Listing destination before opened that. I modified original description.
I found using ODS to create a large CSV file took a LOT longer than using proc export or a data step with a file statement. I can't remember how big my file was, but it reduced from approximately 10 minutes to seconds
I've had the same experience as Tammy. ODS CSV is very resource intensive for big files. Using PROC EXPORT or DATA step is way faster.
Also think of it another way - would you normally PROC PRINT a million row report? The overheads in doing so are much higher compared to a DATA step (PROC EXPORT CSV writes a DATA step behind the scenes).
My Unix has installed on server. How can i export into local machine(C drive) as its pointing automatically to unix path Filename=/opt/app/SAS/config/Lev1/SASApp/C:\Data\class.csv,
But i've specified below
proc export data=&_output.
outfile="C:\Data\class.csv " dbms=csv;
run;
but i've specified
How are you running SAS jobs on your Unix server? From EG or SAS on your desktop or by remotely logging in to your Unix server and then running a Unix SAS session?
Obviously your Unix server knows nothing about your PC C: drive. But if you are using EG or SAS on your desktop both have the capability of downloading server files (amongst other options).
I am running stored process report. I am able to export/import the package into local C drive from/to DI, Also have licence
SAS/ACCESS Interface to PC Files. Any way to map to user local machine who ever accessing the stored process report
In that case a better option would be to get your IT people to set up a common file server folder for this report that is accessible from both your Unix server and your PCs - you should then be able to refer to it using a UNC like \\FileServer\folder1\folder2\MyFile.csv.
Do you have ssh access to the server? If yes, use a tool like WinSCP to copy the files.
The error on the Unix server (=/opt/app/SAS/config/Lev1/SASApp/C:\Data\class.csv) is a known one. 50345 - Changing the current working directory for the SAS® Workspace Server.
Your It support is needed for that. They are also needed with a fully qualified name or offering direct access to some location by your users. Hope they are cooperative.
Seeing just your question I was not able to see a cause. As others pointed to the Slow behavior of ODS CVS, that is an opening to research the data-step approach.
The datastep is often fast. The only thing is you need to create a HTML-table writing to your web-server. The webserver can be accessed using the "_webout" fileref.
The html table output must be codes. It is something like SAS(R) 9.3 Stored Processes: Developer's Guide (Chaining Stored Processes)
I would prefer the users letting have access to the server (Winscp / ssh). Or possible Eguide as it is having FTP included.
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.