SAS - ODS EXCEL multiple sheets macro solution

SAS_ODS_EXCEL_multiple_sheets_macro.sas

/*This illustrates how to pipe a table and graph to ODS EXCEL
using macros. 

 1. Replace BY with WHERE statements 
 2. Add in ODS EXCEL options to name sheet 
 3. Wrap code in macro - note main ods excel statements are outside of the macro.  
 4. Create a list of origins to run this for 
 5. Call macro for each origin 

*/
*sorts your data to use BY logic;
proc sort data=sashelp.cars out=cars;
by origin;
run;



%macro create_report(origin = );
 
 
ods excel options( sheet_name = "&origin.");
 
*displays data in Excel sheet;
proc print data=cars;
where origin = "&origin.";
run;

ods excel options(sheet_interval="NONE" sheet_name = "&origin.");

proc sgplot data=cars;
where origin = "&origin.";
scatter x=mpg_city y=mpg_highway / group = type;
run;

ods excel options(sheet_interval="NOW" sheet_name = "&origin.");

%mend;



*removes proc title and by line which are usually printed by default;
ods noptitle;
options nobyline;
options mprint;

*sets file options - notice use of #BYVAL1 in Sheet Name to control sheet names;
ods excel file='/home/fkhurshed/ODS_Example2.xlsx' style=meadow options(Sheet_interval = "NONE");
 

*create list of origins to run this for;
proc sql;
create table report_list_origins as
select distinct origin
from cars;
quit;

*call macro for each origin;
data _null_;
set report_list_origins;
	str = catt('%create_report(origin=', origin, ');');
	call execute(str);
run;


*closes file;
ods excel close;

if you don't put an extension on your file name 1) GitHub doesn't color your code. 2) I don't think the search functions for Gists registers your code as SAS code. Check me on the last point.

Thanks for the catch on the missing extension @PhilipColtharp!
I'm not sure what you mean on the second point? Does that mean users won't find my SAS code if they search on the main Github page?

I don't know how to check that, but if I search for SAS and see the latest updated Gists, you show up there on top, at the moment. So all's good.

Thanks for the check! When I started using gists/github they didn't initially recognize SAS as a language at the time and I no longer program in SAS so a lot of this is older now too. I only add when someone asks for something these days.... Cheers!