BookmarkSubscribeRSS Feed
sunilreddy
Fluorite | Level 6

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.

15 REPLIES 15
ballardw
Super User

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.

LinusH
Tourmaline | Level 20

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
Reeza
Super User

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?

sunilreddy
Fluorite | Level 6

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

ballardw
Super User

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

sunilreddy
Fluorite | Level 6

Hi ballardw,

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

tammy_dezilva
Quartz | Level 8

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

SASKiwi
PROC Star

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

sunilreddy
Fluorite | Level 6

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

SASKiwi
PROC Star

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

sunilreddy
Fluorite | Level 6

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

SASKiwi
PROC Star

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.

jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 8697 views
  • 1 like
  • 9 in conversation