The SAS Output Delivery System and reporting techniques

Export large dataset to CSV

Reply
Frequent Contributor
Posts: 115

Export large dataset to CSV

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.

Super User
Posts: 11,343

Re: Export large dataset to CSV

Posted in reply to sunilreddy

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.

Super User
Posts: 5,427

Re: Export large dataset to CSV

Posted in reply to sunilreddy

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?

Data never sleeps
Super User
Posts: 19,789

Re: Export large dataset to CSV

Posted in reply to sunilreddy

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?

Frequent Contributor
Posts: 115

Re: Export large dataset to CSV

Posted in reply to sunilreddy

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

Super User
Posts: 11,343

Re: Export large dataset to CSV

Posted in reply to sunilreddy

You have closed the Listing destination but is the HTML destination also open? That will eat a lot of time building an HTML table.

Frequent Contributor
Posts: 115

Re: Export large dataset to CSV

Hi ballardw,

I am closing Listing destination before opened that. I modified original description.

Contributor
Posts: 47

Re: Export large dataset to CSV

Posted in reply to sunilreddy

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

Super User
Posts: 3,252

Re: Export large dataset to CSV

Posted in reply to tammy_dezilva

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).

Frequent Contributor
Posts: 115

Re: Export large dataset to CSV

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

Super User
Posts: 3,252

Re: Export large dataset to CSV

Posted in reply to sunilreddy

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). 

Frequent Contributor
Posts: 115

Re: Export large dataset to CSV

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

Super User
Posts: 3,252

Re: Export large dataset to CSV

Posted in reply to sunilreddy

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.

Super User
Posts: 7,778

Re: Export large dataset to CSV

Posted in reply to sunilreddy

Do you have ssh access to the server? If yes, use a tool like WinSCP to copy the files.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 3,212

Re: Export large dataset to CSV

Posted in reply to sunilreddy

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.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 15 replies
  • 4479 views
  • 1 like
  • 9 in conversation