dm wpgm 'clear log' wpgm;

***********************************************************;
* Multifilebygroupexporter3.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,dir1,multi,finalfil,appender);
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    *;
* files                                         *;
*************************************************;

%do i= 1 %to &counter;

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

proc export data=&&byval&i dbms=&dbms
outfile="&dir1&&byval&i...&ext1" replace;
run;

%end; 

%if &multi =yes %then %do;
 	filename cmds dde 'excel|system';
 	***************************************************************;
 	* Now we start up microsoft excel                             *;
 	* for Office 97 and 2000 use                                  *;
	* x 'c:\program files\microsoft office\office\excel.exe       *;
 	* for Office XP users use                                     *;
	* x 'c:\program files\microsoft office\office10\excel.exe     *;
 	***************************************************************;
 	*>>>>>>>>>>> change the location of the excel.exe  here <<<<< *;
 	x "'c:\program files\microsoft office\office10\excel.exe'";

	%if &appender = append %then %do;
		%let opener=%str(%'[open("&finalfil")]%');
		data startup;
		x=sleep(5);
		run; quit;
		data _null_;
		file cmds;
		put %unquote(&opener);
		run;
		quit;
		%end;

	
 	%do i = 1 %to &counter;
  	%let insname=&dir1&&byval&i...&ext1;
  	%let inserter=%str(%'[workbook.insert("&insname")]%');
    	data _null_;
		file cmds;
		put %unquote(&inserter);
    	run; quit;

 	%end;

 	****************************************************************;
 	* Then we can save the file out here                           *;
 	****************************************************************;
 	* This step saves the file as a new multisheet XLS file        *;
 	data _null_;
 	%let saveas=%str(%'[save.as("&finalfil",1)]%');
 	file cmds;
 	put '[ERROR("FALSE")]';
 	put %unquote(&saveas);
 	put '[quit()]';
 	run; quit;
%end;
%else %do;
	%put 'multisheet routine not called';
%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                  *;
*          DLM delimited file (default delimiter is a blank) .   *; 
*          CSV delimited file (comma-separated values) .CSV      *;
*          TAB delimited file (tab-delimited values) .TXT        *;
* Parameter 7 is the location where you want the resulting files *;
* Parameter 8 is whether you want to create multiple sheet xls   *;
*             files.   Note that Parameter 5 must be set to      *;
*             excel97 or excel200, parameter 6 must be set to    *;
*             xls to use this option.   Set it to yes or no      *;
* Parameter 9 is the directory of the final multisheet file      *;
*             this must be specified whether you are creating    *;
*             the file or not.                                   *;
* Parameter 10 controls whether you want to add to an existing   *;
*              sheet or create a new one                         *;
*              Append means you want to add to an existing sheet *;
*              Replace means you want to create a new one        *;
* Note if the sheet is already there excel will increment        *;
* the sheet by one, for example F F(1) F(2) etc.                 *;
******************************************************************;

%bygroupexport(sashelp.class,sex,character,sheet,excel97,xls,c:\export1\,yes,
               c:\export2\final1.xls,replace);