Nov 8, 2009

SAS macro to split dataset by the number of Observations specified

Suppose there was a large dataset….This SAS macro program splits the dataset by the number of observations mentioned as a parameter……
Usage of the Macro-
%splitdsnbyobs(DatasetName, No ofobservation to split by)
%macro splitdsnbyobs(dsn,splitby);
%RecsInDS(&dsn,no_obs); /* Read more about this macro at */
/*calculate the final obs for each do loop iteration*/
%macro finalobs;
        %if %sysfunc(ceil(%eval(&i * &splitby.))) gt &no_obs %then &no_obs;
        %else %sysfunc(ceil(%eval(&i * &splitby.)));
%mend finalobs;
/* keep the observations from firstobs= and obs=*/
%do i=1 %to %sysfunc(ceil(&no_obs/&splitby));
        data &dsn.&i.;
        set &dsn (firstobs=%sysfunc(floor(%eval((&i.-1)*&splitby.+1))) obs=%finalobs);
%mend splitdsnbyobs;
/* Eg. Create a Dsn with 101 observations */
data loops;
do i=1 to 101;
options symbolgen mprint;
/*Now call the macro to split the observations every 20 records*/
This will output 6 datasets with 20,20,20,20,20,1 observations respectively…

1 comment:

  1. Is there a macro to split a dataset to multiple datasets vertically with a common primary key. For eg, a dataset has 400 fields and 20,000 records. If we can split the dataset into two, with 200 fields and 20,000 records in each dataset with a common field like loan number as primary key would be helpful to load the details for analysis.


Note: Only a member of this blog may post a comment.