SAS - export to SAS and split file by number of records or size of data set

SAS_split_export.sas

*This macro will export a file to a data set and split it based on the number of records per sheet;
%macro export_split (dsn=, size=);
    %*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;
        proc export data= &dsn. (firstobs=&first obs=&last) outfile='/home/fkhurshed/Demo1.xlsx' dbms=xlsx; Sheet="Page&i.";
        run;

        %*Increment counters to have correct first/last;
        %let first = %eval(&last+1);
        %let last = %eval((&i. + 1)*&size.);
    %end;
%mend export;

*Example call;
*After running this, you should find 9 data sets named Split1-Split9;
%export_split(dsn=sashelp.cars, size=50);