PROC EXPORT to .txt taking too long

[SASGeek]

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

[SASKiwi]

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]

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 · Accepted solution]

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]

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]

Thanks for your great solution works great!

Paula