Hi,
I would like to split 15000 rows of data set into 1000 rows data set. The sample data set is below and it goes to 15000 rows. Thank you for your help
nums |
102491 |
127495 |
145603 |
146082 |
151317 |
164886 |
164887 |
164888 |
169898 |
171675 |
174616 |
174617 |
176940 |
177503 |
184793 |
Usually best practice is don't. There are better ways of handling split data sets and 15000 isn't a large/big data by any means.
Assuming you do have some reason, here's a macro I wrote that does it with an example at the end.
/*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);
Quick question: why?
%macro split_dataset;
%do i=1 %to 15; /*number of datasets*/
data want_&i;
set have;
/* 1000 obs per dataset */
if %eval(1000*(&i-1)) <_n_ <=%eval(1000*&i) ;
run;
%end;
%mend split_dataset;
%split_dataset
It is because the system. The system i used does not let me to pull in the data as 15000 row so the max row that i can pull the data is 1000 . I need to use the outputs into query.
Generally, it isn't recommended to split the data into many smaller data sets, you can perform whatever tasks on one large data set, using a BY statement.
data want;
set have;
bygroup = floor((_n_-1)/1000)+1;
run;
proc whatever data=want;
by bygroup;
...
run;
Usually best practice is don't. There are better ways of handling split data sets and 15000 isn't a large/big data by any means.
Assuming you do have some reason, here's a macro I wrote that does it with an example at the end.
/*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);
@Reeza this is perfectly working. Thank you so much. I have a quick question. How to use those 6 outputs in the proc sql steps as variable.
Can you also help me to create macro to use each list (split1-split9) into proc sql step instead using like below;
select * from table where num in(&split1)
select * from table where num in(&split2)
select * from table where num in(&split3)
data have;
do id=1 to 15000;
output;
end;
data temp;
set have;
if mod(_n_,1000)=1 then group+1;
run;
proc freq data=temp noprint;
table group/out=group;
run;
data _nul_;
set group end=last;
call execute(catt('data want_',group,';set temp;if group=',group,';run;'));
run;
An alternative
data _null_;
declare hash h(dataset:'have(obs=0)', multidata:'Y', ordered:'Y');
h.definekey('x');
h.definedata(all:'Y');
h.definedone();
do i=1 by 1 until (i = 1000 | lr);
set have end=lr;
rc=h.add();
end;
n+1;
rc=h.output(dataset:cats('want', n));
rc=h.clear();
run;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.