how to: export a sas dataset into multiple csv files

[arde]

Hi,

I have sas file with 200 rows. I need to split them into 4 rows per csv file.  So I created  variable A and numbered each row sequential from 1 to 200.

I then programmed the following:

ods csv file="export.csv";
proc print data=test (where=(A between 1 and 4)) noobs;
run;

I now have to change the "1" and "4" manually until I get to "197" and "200".  Is there a way to automate this?

Thank you for time

[Kurt_Bremser]

You can do this in a single data step by using the FILEVAR= option of the FILE Statement.

Change the filename every time mod(_n_,4) is 1.

[Tom · Accepted solution]

You can write CSV files using a simple data step, no need for either ODS or PROC EXPORT.

You even use the CALL VNEXT() function to let you write the header row to each file.

So here is an example using SASHELP.CLASS as the example dataset and the current WORK directory as the destination for writing the files that creates a series of files named export1.csv, export2.csv etc.

%let path=%sysfunc(pathname(work));
data _null_;
  do _n_=1 to 4 ;
    set sashelp.class;
    if _n_=1 then link names;
    put (_all_)(+0);
  end;
return;
names:
  length __name_ $32 __fileno 8 __filename $256;
  __fileno+1;
  __filename = cats("&path/export",__fileno,'.csv');
  file csv dsd filevar=__filename;
  do while(1);
    call vnext(__name_);
    if lowcase(__name_) = '__name_' then leave;
    put __name_ @;
  end;
  put; 
run;

So for your problem just change the setting of the macro variable PATH to the place you want to create the file.  Change the SET statement to reference the dataset you want to write.  Can change the line that is building the unique filename to reflect how you want the filenames created.

[arde]

awesome, thank you so much!

[Ksharp]

%let n=4;

data have;
 set sashelp.class end=last;
 if mod(_n_,&n.)=1 then _id_+1;
 if last then call symputx('_id_',_id_);
run;

%macro export_csv(dsn=,path=);
%do i=1 %to &_id_.;
data temp(drop=_id_);
 set &dsn.(where=(_id_=&i.));
run;
proc export data=temp outfile="&path.\export_&i..csv" dbms=csv replace;
run;
%end;
%mend;
%export_csv(dsn=have,path=c:\temp)