topic Re: Storing Huge Data sets to SAS excel files in ODS and Base Reporting

Storing Huge Data sets to SAS excel files

JasonNC — Fri, 22 Nov 2013 20:55:05 GMT

Hi,

I have a data set with 5 million records.I need to pull them into spread sheets.When i splitted the data set into 6 data sets and created CSV it took a long time.It took about 5 hours for each CSV file.

Our back end is unix.I am using SAS EG.The CSV files will be created on our Server.

Is there any better way to do this.

ods csv file="&path.&name..csv";

proc print data=&dataset noobs label;

run;

Re: Storing Huge Data sets to SAS excel files

ballardw — Fri, 22 Nov 2013 21:13:44 GMT

The time was probably caused by using proc print which may have been attempting lots of formatting in the background.

Try using

proc export data=&dataset

outfile="&path.&name..csv"

dbms=CSV

Replace;

run;

if you want variable labels instead of names as column headers add Label to the proc statement.

Re: Storing Huge Data sets to SAS excel files

Tom — Fri, 22 Nov 2013 21:47:56 GMT

A simple data step can create a CSV file. Only tricky part is adding the header row.

proc contents data=&dataset noprint out=contents ; run;

proc sql noprint ;

select quote(trim(coalesce(label,name)))

into :labels separated by " ',' "

from contents

order by varnum

;

quit;

data _null_;

file "&path.&name..csv" dsd lrecl=100000 ;

set &dataset ;

if _n_=1 then put &labels ;

put (_all_) (:) ;

run;

Re: Storing Huge Data sets to SAS excel files

Tal — Sun, 24 Nov 2013 01:09:13 GMT

so how does this code split the set into 6 csv files? I dont understand. Can you please explain?

EDIT: Oh i see.Your code actually works for each of the splitted sets

Re: Storing Huge Data sets to SAS excel files

SASKiwi — Sun, 24 Nov 2013 21:39:14 GMT

In my experience, using ODS for writing very large files is very resource-intensive. The techniques mentioned above are much more efficient.

Re: Storing Huge Data sets to SAS excel files

JasonNC — Mon, 25 Nov 2013 20:02:03 GMT

Hey Guys,

I will try the above methods.Is splitting the file and creating them is correct process or is there any other process?

Re: Storing Huge Data sets to SAS excel files

SASKiwi — Mon, 25 Nov 2013 21:04:58 GMT

The only reason I can see for splitting your files is to stay within the current Excel limit of 1 million rows. You can use the FIRSTOBS = and OBS = dataset options to do this:

data split1;

set large (firstobs = 1 obs = 1000000);

run;

Re: Storing Huge Data sets to SAS excel files

Tom — Tue, 26 Nov 2013 01:24:38 GMT

You can create multiple files in one pass of the data by using the FILEVAR option on the FILE statment.

FILEVAR=variable

defines a variable whose change in value causes the FILE statement to close the current output file and open a new one the next time the FILE statement executes. The next PUT statement that executes writes to the new file that is specified as the value of the FILEVAR= variable.

For example try this program.

%let dataset=sashelp.class ;

%let n=10 ;

%let prefix=~/temp/split ;

proc contents data=&dataset noprint out=contents ; run;

proc sql noprint ;

select varnum

, name

, quote(trim(coalesce(label,name)))

into names

, names

, :labels separated by " ',' "

from contents

order by varnum

;

quit;

data _null_;

set &dataset ;

_group = 1 + int((_n_-1)/&n) ;

_file = cats("&prefix",_group,'.csv');

file csvfile filevar=_file dsd lrecl=100000 ;

if mod(_n_,&n)=1 then put &labels ;

put (&names) (:) ;

run;