***********************************************************;
* bygroupexporter9.sas                                    *;
* Purpose: to create multiple xls files using by group    *;
* variables from a sas dataset                            *;
* It will create multiple sheet .xls files from the by    *;
* group with a switch turned on                           *;
* Version 1.20                                            *;
* Date: 2002/10/30                                        *;
* Restrictions:                                           *;
* By values must have a length of 32 characters or less   *;
* Currently does not work for access datasets             *;
* If someone wants access dataset support contact         *;
* Technical Support                                       *;
* Currently it only supports one by value, if you want    *;
* more than that small modifications would have to be     *;
* made to merge the by values into a unique name          *;
***********************************************************;

options nomacrogen nosymbolgen noxwait noxsync;
options mprint;
%macro bygroupexport(dsname,byvarname,charnum,charname,dbms,ext1,finalfil,dblabel1,wipeit);
*filename cmds dde 'excel|system';
data byvals; set &dsname;
charname="&charname";
run;

*************************************************************;
* If the by variable is numeric                             *;
* First we strip out any decimal points when creating the   *;
* the sas name for the numeric values                       *;
*************************************************************;

%if &charnum=numeric %then %do;
data byvals; set byvals;
length byvar2 $ 32;
byvar1b=tranwrd(&byvarname,".","_");
byvar2=left(trim(charname))||left(trim(byvar1b));
run; quit;
%end;

*************************************************************;
* If the by variable is character                           *;
* we have to strip out all the unusual characters           *;
* that SAS does not allow when creating the variable names  *;
* this routine translates them back to names sas can use    *;
* Note that this does not touch the original dataset        *;
*************************************************************;


%if &charnum=character %then %do;
data byvals; set byvals;
length byvar2 $ 32;
byvar2=left(trim(&byvarname));
byvar2=tranwrd(TRIM(byvar2),' ','_1_');
byvar2=tranwrd(byvar2,'&','_2_'); 
byvar2=tranwrd(byvar2,"'",'_3_');
byvar2=tranwrd(byvar2,"$",'_4_');
byvar2=tranwrd(byvar2,"~",'_5_');
byvar2=tranwrd(byvar2,"`",'_6_');
byvar2=tranwrd(byvar2,"@",'_7_');
byvar2=tranwrd(byvar2,"#",'_8_');
byvar2=tranwrd(byvar2,"%",'_9_');
byvar2=tranwrd(byvar2,"^",'_10_');
byvar2=tranwrd(byvar2,"*",'_11_');
byvar2=tranwrd(byvar2,"(",'_12_');
byvar2=tranwrd(byvar2,")",'_13_');
byvar2=tranwrd(byvar2,"-",'_14_');
byvar2=tranwrd(byvar2,"_",'_15_');
byvar2=tranwrd(byvar2,"+",'_16_');
byvar2=tranwrd(byvar2,"=",'_17_');
byvar2=tranwrd(byvar2,"{",'_18_');
byvar2=tranwrd(byvar2,"}",'_19_');
byvar2=tranwrd(byvar2,"[",'_20_');
byvar2=tranwrd(byvar2,"]",'_21_');
byvar2=tranwrd(byvar2,"|",'_21_');
byvar2=tranwrd(byvar2,",",'_22_');
byvar2=tranwrd(byvar2,"<",'_23_');
byvar2=tranwrd(byvar2,">",'_24_');
byvar2=tranwrd(byvar2,".",'_25_');
byvar2=tranwrd(byvar2,"?",'_26_');
run; quit;
%end;

***********************************************;
* Then we create a dataset with 1 observation * 
* per unique name                             *;
***********************************************;


proc sort data=byvals nodupkey out=nodupk ;
by descending byvar2;

data setup; set nodupk;
keep &byvarname byvar2 charname;
run;



data _null_;
set setup end=last;
call symput('byval'||left(_n_),trim(byvar2));
call symput('byval2'||left(_n_),trim(&byvarname));
if last then call symput('counter',_n_);
run;

*************************************************;
* this macro loop goes thru the counter and     *;
* exports the sas datasets to individual xls    *;
* sheets in the master file                     *;
*************************************************;

%if &wipeit=destroy %then %do;
systask command "del ""&finalfil"" ";
%end;

%do i= 1 %to &counter;

data &&byval&i; set byvals;
if byvar2="&&byval&i";
run;

* can comment out this routine for debugging *;
* drops intermediate values *;

data &&byval&i; set &&byval&i;
drop charname byvar2;
run;

%if &dblabel1=no %then %do;
proc export data=&&byval&i dbms=&dbms
outfile="&finalfil" replace;
sheet="&&byval&i";
run;
%end; * dblabel = no *;



%if &dblabel1=label %then %do;
libname excel1 excel "&finalfil";
data excel1.&&byval&i; set &&byval&i;
run; quit;
libname excel1 clear;
%end; * dblabel yes *;

%end; 


%mend bygroupexport;

******************************************************************;
* Now to execute the macro itself                                *;
* All parameters must be specified for the macro to run          *;
* Parameter 1 is the dataset name                                *;
* Parameter 2 is the variable you are sorting with               *;
* Parameter 3 is where the variable is character or numeric      *;
* Parameter 4 is what to append on to the numeric variable       *;
*             by default we use sheet and the numeric value      *;
* For example , if you had a by values of 8 and 10               *;
*             we would create sheet8 and sheet10                 *;  
*             If you changed the parameter 4 to month it would   *;
*             now create month8 and month10                      *;
* Parameters 5 and 6 is the dbms you want                        *; 
* to use and the extensions                                      *;
*          WK1 Lotus 1 spreadsheet .WK1                          *;
*          WK3 Lotus 3 spreadsheet .WK3                          *;
*          WK4 Lotus 4 spreadsheet .WK4                          *;
*          EXCEL Excel Version 5 spreadsheet .XLS                *;
*          EXCEL4 Excel Version 4 spreadsheet .XLS               *;
*          EXCEL5 Excel Version 5 spreadsheet .XLS               *;
*          EXCEL97 Excel 97 spreadsheet .XLS                     *;
*          Excel2000 Excel2000 spreadsheet .XLS                  *;
* Parameter 7 is the directory of the final multisheet file      *;
*             this must be specified whether you are creating    *;
*             the file or not.                                   *;
* Parameter 8 is whether to write labels - it can only be used   *;
*             with NEW xls files, not existing ones              *;
*             this is fixed in 9.2 valid values are label and no *; 
*             you can use the destroy parameter to delete an     *;
*             existing one                                       *;
* Parameter 9 is whether to destroy the existing xls file        *;
*             or no.  Valid values are no and destroy            *;
******************************************************************;

%bygroupexport(sashelp.class,   /* name of sas dataset */
               sex,             /* name of variable    */
               character,       /* variable character or numeric */
               sheet,           /* if numeric, the name of the suffix */
               excel97,         /* database type                      */
               xls,             /* database extension                 */
               c:\export2\final1.xls,  /* location of final file      */
               no,             /* write out labels , label or no      */   
               no);            /* destroy existing xls file first , no or destroy */