SAS macro for exporting dataset into multiple csv files

Reply
New Contributor
Posts: 3

SAS macro for exporting dataset into multiple csv files


I have a SAS dataset with over 200K rows, which I want to export into multiple CSV files at 100K rows each using a SAS macro. So far, I had been using Proc Export to do the same and used to keep increasing the number of exports depending upon the data volume with FIRSTOBS and OBS  options.

Can somebody please help me with a sample macro code to get this done.

The CSVs should be generated based upon number of rows divided by 100K so that these get imported directly in an excel sheet using a VBA macro which I already have in place. Any help would be appreciable.

Grand Advisor
Posts: 17,336

Re: SAS macro for exporting dataset into multiple csv files

Search on here, there's several macros that do what you're asking.

EDIT:

Avoid a macro with filevar option:

Grand Advisor
Posts: 9,576

Re: SAS macro for exporting dataset into multiple csv files

%let n=10 ;
data class;
 set sashelp.class;
 if mod(_n_,&n)=1 then n+1;
run;
proc sql;
 create table levels as
  select distinct n from class;
quit;
data _null_;
 set levels;
 call execute(catt('proc export outfile="c:\temp\data',n,'.csv" data=class(where=(n=',n,')) dbms=csv replace;run;'));
run;

Xia Keshan

New Contributor
Posts: 3

Re: SAS macro for exporting dataset into multiple csv files

Thanks so much Xia, let me test this piece of code in my actual code and if I get stuck somewhere, will update here.

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: SAS macro for exporting dataset into multiple csv files

I would however question your logic of splitting the data out to CSV then reading each one into Excel.  Would not a better way round be -> eport all data from SAS to CSV (which has no limitations), then in your VBA macro read in blocks of data at a time and paste it out to new sheets.  I.e.

I=1

do while readnextline is not null

     output to currentsheet

     I=I+1

     if I > 100000 then new sheet

end

New Contributor
Posts: 3

Re: SAS macro for exporting dataset into multiple csv files

well actually you are correct, will need to check if/how it works...really appreciate it! The problem I forsee with this option is the sheets created after the first one might not have the headers.

Contributor
Posts: 32

Re: SAS macro for exporting dataset into multiple csv files

Excel 2010 and 2013 have a maximum row limit of 1048576.

You can write directly to an Excel-format file if you have the PC files product. Just use a libname statement to create a file and then write to it with the data step or proc SQL. Each dataset/table is a separate sheet in the new file.

Ask a Question
Discussion stats
  • 6 replies
  • 653 views
  • 8 likes
  • 5 in conversation