****************************************************************;
*program:  datasplit2excel.sas                                 *;
*purpose:  to split any dataset across multiple spreadsheets   *;
*          with the number of rows you specify                 *;
*date:     Feb 2005                                            *;
*restrictions:  only restricted by the number of sheets        *;
*               excel can create                               *;
*version 9 of sas is required to use this program              *;
*a version 8 of this program will be created if there is demand*;
****************************************************************;



options mprint macrogen symbolgen;

%macro splitter(libname,libref,dsname,sheetname,xlsname,dbms,nrows,replaceit,dblabel);

dm wpgm 'clear log' wpgm;

libname &libref &libname;

data multexp.codetest;
do x = 1 to 250000;
y = ranuni(x);
output; end; run;
*****************************************************************;
* this little routine is used if the customer wants to wipe out *;
* the .xls file before creating a new .xls file                 *;
* V9 by default appends new sheets to the existing .xls file    *;
* rather than wiping out the sheet, we compress the quotes      *;
* so that systask can work                                      *;
*****************************************************************;

data _null_;
xlsname=compress(&xlsname,'"');
call symput('xlsname2',xlsname);
run;


%if &replaceit=1 %then %do;
data _null_;
systask command "del &xlsname2";
run;

%end;

****************************************************************************;
*this routine takes the original dataset and splits it based on the number *;
*of rows specified at macro invocation.  the default is 65535              *;
*which is the maximum number of rows excel can handle                      *;
****************************************************************************;

data byvals; 
set &libref..&dsname;
bygrp=int(((_n_/&nrows)+1)-.00000001);
sheetname=cats("&sheetname",put(bygrp,8.));
run;

proc sort data=byvals nodupkey out=setup (keep= bygrp sheetname) ;
by descending bygrp;
run;

**************************************************;
*this routine tells me how many groups there are *;
**************************************************;

data _null_;
set setup end=last;
call symputx('byval'||left(put(_n_,8.)),sheetname);
if last then call symputx('counter',put(_n_,8.));
run; 

*****************************************************;
* we then create the subset sas datasets for export *;
*****************************************************;
libname xcl &xlsname;

data
%do i=1 %to &counter;
 &&byval&i (drop=bygrp sheetname)
 %end;
;
set byvals;
%do i=1 %to &counter;
if sheetname="&&byval&i" then output &&byval&i;
%end;
run;

***************************************************************;
* setup the libname and datastep here with the dblabel option *;
***************************************************************;

%do i= &counter %to 1 %by -1;
data xcl.&&byval&i (dblabel=&dblabel);
set &&byval&i;
run;

%end;

libname xcl clear;

%mend splitter;

%splitter('K:\jebjur',                        /* location of data library */
           multexp,                           /* libref */
          codetest,                           /* sas dataset to export */
           sheet,                           /* name of the sheets you desire  */
           "c:\testsas\mysplit.xls",          /* name of the .xls file created or appended */
           excel2000,                         /* dbms to use                               */
           65535,                             /* number of rows to fill each sheet (65535 is max) */
           1,                                 /* replace existing xls entirely 1 is yes 0 is no */
           no);                               /* dblabel yes or no */