topic Re: how to: export a sas dataset into multiple csv files in SAS Programming

how to: export a sas dataset into multiple csv files

arde — Sun, 04 Sep 2022 15:05:42 GMT

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

Re: how to: export a sas dataset into multiple csv files

Kurt_Bremser — Sun, 04 Sep 2022 15:15:45 GMT

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.

Re: how to: export a sas dataset into multiple csv files

Tom — Sun, 04 Sep 2022 16:57:45 GMT

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.

Re: how to: export a sas dataset into multiple csv files

arde — Sun, 04 Sep 2022 18:15:41 GMT

awesome, thank you so much!

Re: how to: export a sas dataset into multiple csv files

Ksharp — Mon, 05 Sep 2022 11:43:18 GMT

%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)