topic Re: How to export a large dataset in SAS Programming

How to export a large dataset

lmtamina — Wed, 21 Mar 2018 20:05:59 GMT

Good Afternoon,

What is the recommended solution to export a SAS dataset with 14Million observations and 55 variables . We need to send claims details to our vendor on their FTP site. I am a beginner in SAS , and I have not had any experience exporting a large dataset . I am currently familiar with exporting dataset into Excel and CSV format. Appreciate any suggestions. Thank you as always.

Re: How to export a large dataset

ballardw — Wed, 21 Mar 2018 20:11:04 GMT

My first idea would be Proc Export to CSV file. But that would require the order of the variables to match a column order if there is a specified order that is expected on the FTP site. File size shouldn't matter to CSV as there is no number of records limit imposed by the file type and 55 variables isn't a large number and shouldn't normally have issues.

Re: How to export a large dataset

Kurt_Bremser — Wed, 21 Mar 2018 20:15:24 GMT

Write to filename zip with a delimiter. It's probably the most compact result.

Re: How to export a large dataset

Reeza — Wed, 21 Mar 2018 20:18:34 GMT

I just downloaded and imported a 86 million row CSV.

CSV will work but you'll need to zip it and it'll still be big.

You can consider splitting your files into multiple subsets and then sending them piecemeal.

Not ideal but probably the easiest way to do this.

Here's an example on how to split it into small subsets by size

https://gist.github.com/statgeek/f4eaec3e5ec9666865e663c9e225f09d

Re: How to export a large dataset

lmtamina — Wed, 21 Mar 2018 20:18:44 GMT

Thank you both. I am not familiar with writing to filename zip with a delimiter. Is it possible to give an example or the syntax for this procedure. Will greatly appreciate it!

Re: How to export a large dataset

Kurt_Bremser — Thu, 22 Mar 2018 07:11:04 GMT

See this quick example for sashelp.class:

filename outfile zip "$HOME/sascommunity/class.zip" member="class.csv";

data _null_;
file outfile dlm=',';
set sashelp.class;
if _n_ = 1 then put 'name,sex,age,height,weight';
put
  name
  sex
  age
  height
  weight
;
run;

@lmtamina wrote:

Thank you both. I am not familiar with writing to filename zip with a delimiter. Is it possible to give an example or the syntax for this procedure. Will greatly appreciate it!

Re: How to export a large dataset

mnjtrana — Sun, 25 Mar 2018 09:48:33 GMT

I think importing to csv will get your job done, but your file will be huge, so if you use unix, you zip the file using gz command or use the filename zip statement