***********************************************************;
* bygroupexporter9unix.sas                                *;
* Purpose: to create multiple sheet xls files using by group *;
* variables from a sas dataset                            *;
* Version 1.40                                            *;
* Date: 2010/02/01                                        *;
* Restrictions:                                           *;
* By values must have a length of 32 characters or less   *;
* Currently does not work for access datasets             *;
* 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;
options mprint;
%macro bygroupexport(dsname,byvarname,charnum,charname,dbms,ext1,finalfil);
*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                     *;
*************************************************;


%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;

proc export data=&&byval&i dbms=&dbms
outfile="&finalfil" replace;
sheet="&&byval&i";
run;



%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 you can leave these                  *;
* as XLS in the unix version                                     *;
* Parameter 7 is the directory of the final multisheet file      *;
*             this must be specified whether you are creating    *;
*             the file or not.                                   *;
******************************************************************;

%bygroupexport(sashelp.class,   /* name of sas dataset */
               sex,             /* name of variable    */
               character,       /* variable character or numeric */
               sheet,           /* if numeric, the name of the suffix */
               xls,             /* database type                      */
               xls,             /* database extension                 */
               ~ruzsa/myexporter/final3.xls); /* location of final file      */