How do I split a SAS data set into smaller data sets, each with a specific number of records?
This is a commonly asked question, and the answer is usually don't do this. There are multiple other methods so that's usually the correct response. For cases when it's not, there's a macro below that splits the data sets. Note that these are not equally sized and the last data set may be much smaller than the other data sets. If this is a requirement, I suggest setting the sizes in such a manner that this does not occur.
/*This macro splits a data set into data sets of size N.
The parameters requried are:
1. DSN = input data set name, such as sashelp.cars.
The libname should be included unless the data set
is in the work library.
2. Size = Number of records to be included in each data
set. Note that the last data set will be truncated,
ie if only 28 records are available only 28 will be
written to the output data set.
3. outDsnPrefix = Name of output data sets, will be indexed as
outDSNPrefix1
outDSNPrefix2
outDSNPrefix3
*/
%macro split (dsn=, size=, outDsnPrefix=Split);
%*Get number of records and calculate the number of files needed;
data _null_;
set &dsn. nobs=_nobs;
call symputx('nrecs', _nobs);
n_files=ceil(_nobs/ &size.);
call symputx('nfiles', n_files);
stop;
run;
%*Set the start and end of data set to get first data set;
%let first=1;
%let last=&size.;
%*Loop to split files;
%do i=1 %to &nfiles;
%*Split file by number of records;
data &outDsnPrefix.&i.;
set &dsn. (firstobs=&first obs=&last);
run;
%*Increment counters to have correct first/last;
%let first = %eval(&last+1);
%let last = %eval((&i. + 1)*&size.);
%end;
%mend split;
*Example call;
*After running this, you should find 9 data sets named Split1-Split9;
%split(dsn=sashelp.cars, size=50, outDsnPrefix=Split);
SAS programmers are often asked to break large data sets into smaller ones. Conventional wisdom says that this is also a pointless chore, since you can usually achieve what you want (that is, process a certain subset of data) by applying a WHERE= option or FIRSTOBS=/OBS= combination. Splitting a data set creates more files, which occupy more disk space and forces more I/O operations. I/O and disk access is often the most expensive part of your SAS processing, performance-wise.
But, if you must split a data set, and you want divide the data via a BY group value (not just into evenly sized record-chunks), here's a different macro to do that.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.