topic Re: Best / Fastest way to create a CSV from a dataset? in SAS Programming

Best / Fastest way to create a CSV from a dataset?

StaticFX — Mon, 13 Nov 2017 18:15:09 GMT

Currently I am using:

ODS CSV FILE="DISP_PRE1882.csv" TYPE=csvall PATH="/home/xxxx";

PROC PRINT DATA=COMBINED3;

RUN;

ODS CSV close;

wondering if there is a faster way to do this? The Dataset has amost 1 million rows and it can take a while...

Thanks!!

-Geoff

Re: Best / Fastest way to create a CSV from a dataset?

ballardw — Mon, 13 Nov 2017 21:17:17 GMT

Proc export data=combined

outfile="DISP_PRE1882.csv" /* second = character removed*/

dbms=CSV

replace

;

run;

If you want the variable labels instead of the names add LABEL before the ; on the proc statement.

I do recommend always using a fully qualified path instead of just the out file name as the folder it ends up in may be problematic for some uses.

Re: Best / Fastest way to create a CSV from a dataset?

StaticFX — Mon, 13 Nov 2017 18:25:42 GMT

Ok, so I add the full path in the outfile?

and why is there 2 ==?

Re: Best / Fastest way to create a CSV from a dataset?

Reeza — Mon, 13 Nov 2017 20:36:15 GMT

@StaticFX its a typo, a single = is fine.

Re: Best / Fastest way to create a CSV from a dataset?

Tom — Mon, 13 Nov 2017 20:55:50 GMT

Just use a data step.

data _null_ ;
  file "/home/xxxx/DISP_PRE1882.csv" dsd ;
  set COMBINED3;
  put (_all_) (+0);
run;

If you need to add a header row you can generate that first and then use the MOD option on the FILE statement in the data step above.

proc transpose data=COMBINED3(obs=0) out=names;
  var _all_;
run;
data _null_ ;
  file "/home/xxxx/DISP_PRE1882.csv" dsd ;
  set names end=eof;
  put _name_ @ ;
  if eof then put;
run;

Re: Best / Fastest way to create a CSV from a dataset?

Ksharp — Tue, 14 Nov 2017 12:56:55 GMT

Or Check MACRO %ds2csv()

Re: Best / Fastest way to create a CSV from a dataset?

StaticFX — Tue, 14 Nov 2017 14:45:13 GMT

Good lord its like night and day! this took 11.9 seconds to write 1.02m records to the file! Plus it was 27mb smaller... Thanks so much!

I havent tried the others to compare, but no need now lol

Re: Best / Fastest way to create a CSV from a dataset?

Tom — Tue, 14 Nov 2017 15:01:29 GMT

You also might try writing directly to a compressed file and see if that makes it faster. The computer will probably spend more CPU cycles compressing the file, but much less time will be spend waiting for the data to be written to the disk.

You could use the ZIP filename engine. And if you have at least SAS 9.4 M5 release you can use GZIP format which should be a little faster as it does not have the overhead of multiple file archives like ZIP format.

%let path=%sysfunc(pathname(work));

filename csv "&path/class.txt";
proc export data=sashelp.class file=csv dbms=csv ; run;

filename csvgz zip "&path/class.txt.gz" gzip ;
proc export data=sashelp.class file=csvgz dbms=csv ; run;

filename csvzip zip "&path/class.zip" member='class.txt' ;
proc export data=sashelp.class file=csvzip dbms=csv ; run;