@mraza12 wrote:
I would want to create the file outside of the macro which means I should make those variables global. I'd appreciate a sample code if anyone would like to pitch in.
Here's a fully automated version.
This example creates a workbook for each Origin in the cars data set and each workbook has an individual named sheet for the Makes. It uses ODS EXCEL and BY group processing so you don't need to know the number of makes or origins and is fully data driven.
%*Generate sample data to work with here;
proc sort data=sashelp.cars out=cars;
by origin make;
run;
*Close other destinations to improve speed;
ods listing close;
ods html close;
*macro that exports to file with Origin in file name and a
sheet for each make. The number of origins or makes is not
needed ahead of time;
%macro export_origin(origin=);
%*filename for export, set style for fun and add label for each sheet;
ods excel file="C:\_localdata\Cars_&origin..xlsx"
style = meadow
options(sheet_interval='bygroup'
sheet_label='Make');
*generate a sheet for each make (by make);
proc print data=cars noobs label;
where origin = "&Origin";
by make;
run;
%*close excel file;
ods excel close;
%mend;
*calls macro for each origin in file.
number of origins doesn't need to be known ahead of time;
data _null_;
set cars;
by origin;
if first.origin then do;
*create macro call;
str = catt('%export_origin(origin=', origin, ');');
*call macro;
call execute(str);
end;
run;
%*reopens output destinations;
ods html;
ods listing;
https://github.com/statgeek/SAS-Tutorials/blob/master/SAS_export_multiple_workbook_worksheet.sas
... View more
