*************************************************************************;
* Macroexporternew.sas                                                  *;
* Version 1.13                                                          *;
* Date January 18 2002                                                  *;
* Date Last Modified Feb 15 2002                                        *;
* Purpose:  to export multiple SAS datasets to multiple sheets in excel *;
* THIS DOES NOT UPDATE EXISTING WORKBOOKS, THAT IS                      *;
* Macroexporterexisting.sas                                             *;
*                                                                       *;
* MAKE SURE YOU MAKE ALL CHANGES WHERE YOU SEE THE ARROWS >>>>>>>>      *;
* SCAN THE PROGRAM FOR THESE                                            *;
*                                                                       *;
* Limitations:                                                          *;
* Program cannot be run on remote connect or internet                   *;
* servers where there is no display terminal                            *;
* You will need to change the excel.exe location depending on           *;
* where you installed Microsoft office                                  *;
* While this was customized for excel, it could be modified             *;
* for Quattro Pro but not Lotus 1-2-3 which does not support DDE        *;
* Dataset names WORK.SETUP or WORK.DELETER may not be used              *;
*                                                                       *;
* Modifications:                                                        *;
* 15 Feb 2002 - Changed call system command to handle spaces in         *;
* directory structures                                                  *;
* Changed X command for quoting to start excel a bit easier             *;
*************************************************************************;

* dm wpgm 'clear log' wpgm;

* These options are used for debugging *;
* You can turn these off if you wish   *;
options mprint macrogen nosymbolgen;

*These options must remain in place    *;
*in order for the program to run       *;
options noxwait noxsync;

********************************************;
* Now to start the macro                   *;
* the first loop here takes all the        *;
* dataset names and figures out how many   *;
* there are and creates a counter          *;
* see the actual macro call for the        *;
* explanation of the parameters            *;
********************************************;
 %macro exloop(dir1,savefile,deltemp);
data _null_;
set setup end=last;
call symput('dsnamem'||left(_n_),trim(dsname1));
call symput('dsnamem2'||left(_n_),trim(dsname2));
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;
%put &&dsnamem&i;
proc export data=&&dsnamem&i dbms=excel2000
outfile="&dir1&&dsnamem2&i...xls" replace;
run;
%end;
 
*********************************************;
* Then we start the DDE loop itself         *;
* NOTE THE LOCATION OF MICROSOFT OFFICE     *;
* YOU MAY NEED TO CHANGE THIS TO MATCH YOUR *;
* LOCATION                                  *;
* If the first dataset is encountered it    *;
* Simply opens that xls file                *;
* Subsequent loops insert into the first    *;
* XLS file and saves it to a new XLS file   *;
* when complete                             *;
*********************************************;


filename cmds dde 'excel|system';

%do i= 1 % to &counter;
	%if &i=1 %then %do;
***************************************************************;
* 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'";
	%let fullname=&dir1&&dsnamem2&i...xls;
	%let opener=%str(%'[open("&fullname")]%');
	data startup;
	x=sleep(5);
	run; quit;
	data _null_;
	file cmds;
	put %unquote(&opener);
	run;
	quit;
	%end;
%else %do;
    %let insname=&dir1&&dsnamem2&i...xls;
	%let inserter=%str(%'[workbook.insert("&insname")]%');

    data _null_;
	file cmds;
	put %unquote(&inserter);
	%end;

%end;

* This step saves the file as a new multisheet XLS file *;
data _null_;
%let saveas=%str(%'[save.as("&savefile",1)]%');
file cmds;
put %unquote(&saveas);
put '[quit()]';
run; quit;

* Now to build a bat file to clean up the temporary files *;
%if &deltemp=yes %then %do;
	%let file1a=del1.bat;
	%let file1b=&dir1;
	%let filecmd=&dir1&file1a;


	filename del1 "&filecmd";
	data deleter; set setup;
	delvar='"'||left(trim("&dir1"))||left(trim(dsname2))||'.xls'||'"';
	delvar2='del '||delvar;
	file del1;
	put delvar2;
	run;
	quit;

* Then delete the files                           *;
* If you choose not to delete the files it writes *;
* a message to the log for the location           *;

	data _null_;
	call system(%unquote(%str(%'%"&filecmd%"%')));
	run; quit;
	%end;
	%else %do;
	%put "Temporary files are still in &dir1";
	%end;




%mend exloop;  

******************************************************************;
* This part loads the datasets that are turned into individual   *;
* sheets in the excel workbook.    All datasets names MUST       *;
* have 2 level names.  Temporary datasets should use a WORK.     *;
* prefix, such as WORK.TEMP1                                     *;
* You may not use the names WORK.DELETER or WORK.SETUP as        *;
* these are used by the macro itself                             *;
******************************************************************;

data setup;
length dsname1 $ 35;
input dsname1 $;
* >>>>>>>> Change the dataset names here after the cards statement *;
cards;
sashelp.class
sashelp.air
;

data setup; set setup;
dsname2=scan(dsname1,2);
run; quit;

**********************************************************;
* This step deletes the existing spreadsheet and calls   *;
* the macro to create a new workbook with multiple sheet *;
* the file listed in the x command must match the 2nd    *;
* parameter in the macro %exloop below                   *;
**********************************************************;

* >>>>>>> Change the name here  <<<<<<<<<<<<<<<<<<<<<<<<<*;

x 'del c:\my data\final1.xls';

**********************************************************;
* Now we call the macro here                             *;
* The first parameter tells where to store the temporary *;
* xls files,  the 2nd parameter is where the final       *;
* dataset should be store, the 3rd parameter controls    *;
* whether the temporary xls files should be deleted at   *;
* completion or not.  Yes means delete temporary xls     *;
* files, no means not to delete the temporary xls files  *;
* You should specify directories that exist the program  *;
* will not make them for you.                            *;
**********************************************************;

* >>>>>>>> Change the parameters here <<<<<<<<<<<<<<<<<<<*;

%exloop(c:\my data\,c:\my data\final1.xls,yes);

run;