topic Re: export large dataset to multiple csv files in SAS Programming

export large dataset to multiple csv files

jdmarino — Fri, 18 Oct 2013 17:19:38 GMT

I need to export a very large dataset to csv, but I need to break the output into multiple, smaller files for the downstream process. The split need to incorporate logic so that certain groups of records are not split across files. Can anyone suggest ways to do this?

My current plan: look at what proc export sticks in the log (it seems to be a macro, despite the name), scrape that code and doctor it do the split logic. I'm pretty sure this will work (I haven't seen the spec for the logic yet), but there may be a less hacky way. If so, RSVP.export

Re: export large dataset to multiple csv files

Reeza — Fri, 18 Oct 2013 17:44:08 GMT

The support.sas.com site has several macros that will split files by groups.

38430 - Exporting BY groups from a SAS data set to multiple Microsoft Excel or text files

I don't know if you have a true "BY" group though without more details.

I think this code can also be modified to export to CSV simply enough:

Split Data into Subsets - sasCommunity

Re: export large dataset to multiple csv files

Cynthia_sas — Fri, 18 Oct 2013 17:52:42 GMT

Hi: Rather than tinkering with PROC EXPORT, I would use the NEWFILE=BYGROUP with ODS CSV. There's a code example below. In the first example, a simple BY AGE was used in PROC PRINT to create a separate output file for each age. In the second example, I used a DATA step program to make some groups using IF logic, then I use the new group variable in my BY statement. In the second example, you should get 3 output files.

cynthia

proc sort data=sashelp.class;
by age;
run;
title; footnote;

** 1) will get 1 csv file for each age;
ods csv file='c:\temp\age11.csv'
newfile=bygroup;

    proc print data=sashelp.class;
    by age;
    run;
ods csv close;

** 2) use my logic to make groups;
data newfile;
** create new groups using logic;
set sashelp.class;
if age in (11,12) then
     mygrp = 1;
else if age in (13, 14) then
     mygrp = 2;
else mygrp = 3;
run;

proc sort data=newfile;
by mygrp;
run;

** will make mgrp1, mgrp2, mgrp3 files;
ods csv file='c:\temp\mgrp1.csv'
    newfile=bygroup;

    proc print data=newfile;
    by mygrp;
    run;
ods csv close;

Re: export large dataset to multiple csv files

Tom — Fri, 18 Oct 2013 18:19:36 GMT

CSV files are trivial to write with a SAS datastep. If you ignore the header record you just need:

data _null_;

set MYDATA ;

file MYFILE dsd dlm=',' lrecl=32000 ;

put (_all_) (:) ;

run;

To make multiple files you can use the FILEVAR option on the FILE statement to control the name of the target file with data step logic.

You can build macro variables with the list of variable names from the metadata (SASHELP.VCOLUMN or use PROC CONTENTS) to drive the put statement. You can also build a macro variable with valid code to put the variable names (or labels) as the header.

You can use a double DOW loop to count how many records you have per by group before you start the group so that you can decide if you should start a new file before the current BY value.

So something like this: (UNTESTED)

%let varlist=A B C;

%let titles="A" ',' "B" ',' "C" ;

%let maxobs=10000 ;

data _null_;

do nobs=1 by 1 until (last.group);

set mydata ;

by group ;

end;

do until (last.group) ;

set mydata ;

by group ;

if nrecs=0 or (nrecs + nobs > &maxobs) then do;

filenumber+1;

nrecs=0;

filename = cats('myfile',filenumber,'.csv');

end;

file dummy filevar=filename dsd dlm=',' lrecl=32000 ;

nrecs+1;

if nrecs=1 then put &titles;

put &varlist ;

end;

run;

Re: export large dataset to multiple csv files

jdmarino — Fri, 18 Oct 2013 18:26:32 GMT

I've never used ODS, and this seems like the newthink. When I learn more about the split logic, I'll give this a try.

Re: export large dataset to multiple csv files

jdmarino — Fri, 18 Oct 2013 18:32:24 GMT

This blows my mind. I don't know how to think about iterating over set and by statements. Are you looping over the input multiple times?

Re: export large dataset to multiple csv files

jdmarino — Fri, 18 Oct 2013 18:34:42 GMT

I do a lot of macro programming, so this approach is, at least, familiar territory. I think I'm going to try the ODS solution, but I worry that I will have mygrp in the output (and it wasn't in the original input).

Re: export large dataset to multiple csv files

Tom — Fri, 18 Oct 2013 18:38:54 GMT

It is reading the input dataset twice. But it does it in one pass with two separate pointers (one for each SET statement). Unless your BY groups have a lot of records disk cache or operating system buffering will make it use the same number of physical disk reads as a single pass.

Re: export large dataset to multiple csv files

Tom — Fri, 18 Oct 2013 18:41:14 GMT

Without a VAR statement all variables except the ones listed in the BY statement will appear.

Use OPTIONS NOBYLINE to suppress any BY line that PROC PRINT might generate.

Re: export large dataset to multiple csv files

jdmarino — Fri, 18 Oct 2013 18:47:07 GMT

I've never done this. I've always used drop= or keep= on the input dataset to get the columns I want. (Any way, I will have to enumerate the columns I want, which means being aware of them. It would be nice to avoid that: drop= would only need to know about the columns I create. But I can't BY process on a dropped column.)

It's always good to post here and get help.

Re: export large dataset to multiple csv files

UrvishShah — Mon, 21 Oct 2013 09:00:53 GMT

Hi,

If you split the data in logical way so that it won't misinterpreat any values, it will be tedious task as in a big data user can't easily make the group based on values...

So my try is to use the attached SAS Macro which creates the one CSV file with a different sheets correspond to each unique values of the variable...It will work for any variable (Charcter or Numeric)...

If this one is not meeting requirement, please let us know the approx observation so that we can think about new logic...

-Urvish

Re: export large dataset to multiple csv files

jdmarino — Mon, 21 Oct 2013 12:43:45 GMT

Thanks, Urvish; it's generous of you to share. I don't yet know the split logic (I'm helping a friend and he hasn't yet provided me the spec). But more importantly, I'm not exporting to excel files, but to actual .CSVs, thus the multisheet approach does not apply. (A .csv is a pure text file; there are no sheets.)

Re: export large dataset to multiple csv files

jdmarino — Mon, 28 Oct 2013 16:47:01 GMT

Cynthia,

I'm trying your solution because it seems require the least coding on my part. I have to say, however, that ODS is a multi-headed monster, and I can't find anything in the help about "ods csv". (I can find "ods csvall", but that's a different beast.) Can you point me to some documentation?

Second question: I'd like NULLs in my dataset to be represented by consecutive delimiters (i.e. an empty field), but I'm getting a '.' (missing). Can you tell me how to turn off the dot?

Re: export large dataset to multiple csv files

art297 — Mon, 28 Oct 2013 17:06:48 GMT

One way regarding how to "turn off" the dots:

options missing=' ';

Some documentation: I got a number of related hits doing a Google search on:

ods csv sas

Re: export large dataset to multiple csv files

jdmarino — Mon, 28 Oct 2013 17:13:06 GMT

Thanks.

I'd like to write a general purpose macro (although that's a secondary priority),so I'd certainly prefer to only turn off the dots for the proc print and not in general. Maybe I can detect the state of that option and restore it after I do my work.

edit:

%let missopt = %sysfunc(getoption(missing,keyword)); /* store the existing option away */

options missing='';

...

options &missopt;

This seems to be what I wanted.

Interesting about the google search; I've been searching both my local help file and support.sas.com and not getting good results. (I get results, just not relevant one.)

Re: export large dataset to multiple csv files

jdmarino — Mon, 28 Oct 2013 21:24:42 GMT

Well, I was disappointed today. I tested my code on a 100K row dataset, splitting into 10k chunks but not splitting across a special set of vars. It worked.

BUT, when I ran it on the real 47M row dataset, I got

ERROR: The SAS System stopped processing this step because of insufficient memory.

on a machine with 24Gig of RAM and a Terabyte of drive space.

Now, to try Tom's approach.

Re: export large dataset to multiple csv files

art297 — Mon, 28 Oct 2013 21:31:38 GMT

Just out of curiosity I have two questions: (1) Did you sort your real dataset before running the code? AND (2)does your work directory happen to be on a drive that has very limited memory?

Re: export large dataset to multiple csv files

Peter_C — Mon, 28 Oct 2013 23:20:21 GMT

ods csv involves far mode than you need

Other ODS destinations take better advantage of the overheads of the destination approach.

To generate a csv file requires very little memory

Re: export large dataset to multiple csv files

Peter_C — Mon, 28 Oct 2013 23:22:37 GMT

it is worth getting over the apparent complexity (of Tom's method), when you have 47M rows that cause a lot of difficulty for the apparently "simpler" ODS approach

Re: export large dataset to multiple csv files

jdmarino — Wed, 30 Oct 2013 17:52:33 GMT

Yep.

I've got the basics working (had problems when the BY group was larger than the row threshold for a file). I'm about to research dataset introspection so I can build a list of the fields to output. _all_ is no good for me because of the extra fields I added.