topic Re: PROC EXPORT to .txt taking too long in SAS Procedures

PROC EXPORT to .txt taking too long

SASGeek — Thu, 16 Jun 2022 01:29:21 GMT

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

Re: PROC EXPORT to .txt taking too long

SASKiwi — Thu, 16 Jun 2022 01:48:15 GMT

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.

Re: PROC EXPORT to .txt taking too long

ballardw — Thu, 16 Jun 2022 02:02:32 GMT

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.

Re: PROC EXPORT to .txt taking too long

Tom — Thu, 16 Jun 2022 02:32:22 GMT

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;

Re: PROC EXPORT to .txt taking too long

ballardw — Thu, 16 Jun 2022 02:39:55 GMT

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.

Re: PROC EXPORT to .txt taking too long

SASGeek — Fri, 17 Jun 2022 16:29:50 GMT

Thanks for your great solution works great!

Paula