*-> Create A Few Worksheets;

ods noresults;
ods listing close;
ods html body="c:\temp\classods.xls";
proc print data=sashelp.class(obs=10);
run;
ods html close;
ods html body="c:\temp\shoesods.xls";
proc print data=sashelp.shoes(obs=10);
run;
ods html close;
ods html body="c:\temp\zipcodeods.xls";
proc print data=sashelp.zipcode(obs=10);
run;
ods html close;
ods listing;
ods results;

*-> Macro to Combine Worksheets;

%macro many2one(in=,out=);

options noxwait;
x erase "&out";
options xwait;

data _null_; 
file "c:\temp\class.vbs";
put 'Set XL = CreateObject("Excel.Application")' /
    'XL.Visible=True';
%let n=1;
%let from=%scan(&in,&n," ");
%do %while("&from" ne "");
  %let fromwb=%scan(&from,1,"!");
  %let fromws=%scan(&from,2,"!");
  put "XL.Workbooks.Open ""&fromwb""";
  %if &n=1 %then 
     put "XL.ActiveWorkbook.SaveAs ""&out"", -4143"%str(;);
  %else %do; 
     put "XL.Workbooks(""%scan(&fromwb,-1,'\')"").Sheets(""&fromws"").Copy ,XL.Workbooks(""%scan(&out,-1,'\')"").Sheets(%eval(&n-1))";
     put "XL.Workbooks(""%scan(&fromwb,-1,'\')"").Close";
	 %end;
  %let n=%eval(&n+1);
  %let from=%scan(&in,&n, " ");
%end;
put "XL.Workbooks(""%scan(&out,-1,'\')"").sheets(1).activate";
put "XL.Workbooks(""%scan(&out,-1,'\')"").Save";
put "XL.Quit";
run;

x 'c:\temp\class.vbs'; 
%mend;

*-> Example;

%many2one(in=c:\temp\classods.xls!classods
             c:\temp\shoesods.xls!shoesods
             c:\temp\zipcodeods.xls!zipcodeods,
          out=c:\temp\combined.xls);