BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASGeek
Obsidian | Level 7
Hello,
I have a SAS file of over 2m records (and growing) that takes 2 hrs + to create a .txt file. The code is basic
proc export data=work.cust_data
outfile= "c:\cust_data.txt"
dbms=tab
replace;
run;

Are we stuck with the 2 hr wait time or is there something we can change ? If so, please provide example.

Thank you

Paula
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

PROC EXPORT is reasonably efficient.  I suspect the real issue is the disk where you are writing the text file.

 

Since you are writing the same file over and over you could eliminate the small amount of overhead that PROC EXPORT does add and just write your own data step to write the file instead.

 

If you don't need to header line it is very trivial.

data _null_;
  set cust_data;
  file "c:\cust_data.txt" dsd dlm='09'x ;
  put (_all_) (+0);
run;

If you do need to include the header line it is trivial if the structure never changes.

data _null_;
  set cust_data;
  file "c:\cust_data.txt" dsd dlm='09'x ;
  if _n_=1 then put 'var1' '09'x 'var2' ..... ;
  put (_all_) (+0);
run;

And if it does change it does not really add much either.

proc transpose data=cust_data(obs=0) out=names;
  var _all_;
run;
data _null_;
  set names ; 
  file "c:\cust_data.txt" dsd dlm='09'x ;
  put _name_ @;
run;
data _null_;
  set cust_data;
  file "c:\cust_data.txt" dsd dlm='09'x mod ;
  put (_all_) (+0);
run;

 

View solution in original post

5 REPLIES 5
SASKiwi
PROC Star

Where are you running this? On a PC or on a remote SAS server? If it is on a PC then you will be limited by the IO performance of your disk drive. Please note that if your PC only has one disk drive then it will be doing both the reading and writing of the data at the same time.

 

Please post the SAS log of your PROC EXPORT program step. If there is a big difference between the real time and CPU time reported, then IO is the likely culprit.

 

One option would be to split the SAS dataset in two and process each half in two SAS sessions / jobs running at the same time. That should cut your time nearly in half. The disadvantage is that you will end up with two tab-delimited files.

ballardw
Super User

How many variables?

Does whatever you use the export for require all the variables?

 

If you export to a remote drive, cloud or similar you also may run into any bottleneck imposed by that communication or network bandwidth.

 

Tom
Super User Tom
Super User

PROC EXPORT is reasonably efficient.  I suspect the real issue is the disk where you are writing the text file.

 

Since you are writing the same file over and over you could eliminate the small amount of overhead that PROC EXPORT does add and just write your own data step to write the file instead.

 

If you don't need to header line it is very trivial.

data _null_;
  set cust_data;
  file "c:\cust_data.txt" dsd dlm='09'x ;
  put (_all_) (+0);
run;

If you do need to include the header line it is trivial if the structure never changes.

data _null_;
  set cust_data;
  file "c:\cust_data.txt" dsd dlm='09'x ;
  if _n_=1 then put 'var1' '09'x 'var2' ..... ;
  put (_all_) (+0);
run;

And if it does change it does not really add much either.

proc transpose data=cust_data(obs=0) out=names;
  var _all_;
run;
data _null_;
  set names ; 
  file "c:\cust_data.txt" dsd dlm='09'x ;
  put _name_ @;
run;
data _null_;
  set cust_data;
  file "c:\cust_data.txt" dsd dlm='09'x mod ;
  put (_all_) (+0);
run;

 

ballardw
Super User

Maybe instead of writing the entire set use a data step to write to the existing file with MOD option and use the OBS= data set option on the SET statement to only include the "new" observations.

 

Of course, if you are adding variables or changing values of the already ready variables this isn't possible.

SASGeek
Obsidian | Level 7

Thanks for your great solution works great!

 

Paula

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 705 views
  • 0 likes
  • 4 in conversation