topic Re: Export large dataset to CSV in ODS and Base Reporting

Export large dataset to CSV

sunilreddy — Tue, 13 May 2014 20:03:18 GMT

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.

Re: Export large dataset to CSV

ballardw — Tue, 13 May 2014 20:40:31 GMT

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.

Re: Export large dataset to CSV

LinusH — Tue, 13 May 2014 20:41:58 GMT

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?

Re: Export large dataset to CSV

Reeza — Tue, 13 May 2014 20:48:02 GMT

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?

Re: Export large dataset to CSV

sunilreddy — Tue, 13 May 2014 21:32:13 GMT

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

Re: Export large dataset to CSV

ballardw — Tue, 13 May 2014 22:03:08 GMT

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

Re: Export large dataset to CSV

sunilreddy — Tue, 13 May 2014 22:13:33 GMT

Hi ballardw,

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

Re: Export large dataset to CSV

tammy_dezilva — Tue, 13 May 2014 22:31:05 GMT

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

Re: Export large dataset to CSV

SASKiwi — Tue, 13 May 2014 22:46:34 GMT

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

Re: Export large dataset to CSV

sunilreddy — Tue, 13 May 2014 23:27:34 GMT

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

Re: Export large dataset to CSV

SASKiwi — Wed, 14 May 2014 00:04:23 GMT

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

Re: Export large dataset to CSV

sunilreddy — Wed, 14 May 2014 00:10:41 GMT

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

Re: Export large dataset to CSV

SASKiwi — Wed, 14 May 2014 02:19:33 GMT

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.

Re: Export large dataset to CSV

Kurt_Bremser — Wed, 14 May 2014 06:24:37 GMT

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

Re: Export large dataset to CSV

jakarman — Wed, 14 May 2014 07:34:04 GMT

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.

Re: Export large dataset to CSV

Cynthia_sas — Wed, 14 May 2014 13:52:13 GMT

Hi:

In addition to all of the other suggestions, I have a different one. When you are using %stpbegin, you do NOT need to repeat your ODS invocation statements:

%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;

In addition, your invocation statement is unnecessary because there is no "sheet_name" suboption for ODS CSV or ODS CSVALL, so your "extra" statement will have no impact on your output. So, aside from all the other issues, you are doing something that is not possible with the CSV destination.

cynthia