Hi all:
I tried to ODS proc report multiple datasets to One excel file but with different sheets
the code is not working. any fix?
thanks
%macro mm2(ds); title; footnote; ods results off; ods listing close; ods escapechar="^"; ods excel file="C:\rpt.xlsx" style=excel options (row_repeat="header" frozen_headers="ON" frozen_rowheaders="OFF" ); %if &ds=sashelp.cars %then %do; ods excel options (sheet_name = "A"); proc report data=sashelp.cars nowd split='~' missing style(header)=[just=left] style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box]; %IF &num > 0 %Then %Do; column make ; define make /"make"; run; %End; %Else %if &num=0 %then %Do; proc report data = noobs; column x; define x / display ""; run; %End; %end; %if &ds=sashelp.class %then %do; ods excel options(autofilter="ALL" sheet_name="C"); proc report data=sashelp.class nowd split="~" missing style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box] ; column name ; define name /"name" ; run; %End; %Else %if &num=0 %then %Do; proc report data = noobs; column x; define x / display ""; run; %End; %end; %if &ds=sashelp.shoes %then %do; ods excel options(autofilter="ALL" sheet_name="d"); proc report data=sashelp.shoes nowd split="~" missing style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0]; %IF &num. > 0 %Then %Do; column region ; define region/ "region"; run; %End; %Else %Do; proc report data =noobs; column x; define x / display ""; run; %End; %end; /*%MACRO mm(ds); */ %mend; %mm2(sashelp.car); %mm2(sashelp.class); %mm2(sashelp.shoes); ods excel close; ods results on; ods listing;
you are right, I am stuck with the overwriting part. I have to took the first ods part out of the macro and manipulate the other parts of the code.
BTW: By adding "Sheet_interval="PROC" did work nicely,
thanks all
title; footnote; ods results off; ods listing close; ods escapechar="^"; ods excel file="C:\rpt.xlsx" style=excel options (row_repeat="header" /*to output to different sheets*/ sheet_interval="PROC"); %macro mm2(ds); %if &ds=sashelp.cars %then %do; ods excel options (sheet_tname = "A"); proc report data=sashelp.cars nowd split='~' missing style(header)=[just=left] style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box]; column make ; define make /"make"; run; %end; %else %if &ds=sashelp.class %then %do; ods excel options(autofilter="ALL" sheet_name="C"); proc report data=sashelp.class nowd split="~" missing style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box] ; column name ; define name /"name" ; run; %end; %else %if &ds=sashelp.shoes %then %do; ods excel options(autofilter="ALL" sheet_name="d"); proc report data=sashelp.shoes nowd split="~" missing style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0]; column region ; define region/ "region"; run; %end; /*%MACRO mm(ds); */ %mend; %mm2(sashelp.cars ); %mm2(sashelp.class ); %mm2(sashelp.shoes ); ods excel close; ods results on; ods listing;
I think you may want to add: Sheet_interval='PROC' to your first ODS Excel options list. That should create one sheet for each procedure call as long as you do not override Sheet_interval setting.
HOWEVER: You would have to set the file outside of your macro. Currently you recreate the Excel file each time you call the macro because inside the macro you set the file name. Which overwrites the previous version each time you call the macro.
You also have a "not defined to us" macro variable &num so I have no idea how that would impact this.
Better would be to extract just the REPORT sections of the code and have the ODS Excel with the File name option outside and then close the file apart from the macro.
This would look more like:
%macro mm2(ds); %if &ds=sashelp.cars %then %do; ods excel options (sheet_name = "A"); proc report data=sashelp.cars nowd split='~' missing style(header)=[just=left] style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box]; %IF &num > 0 %Then %Do; column make ; define make /"make"; run; %End; %Else %if &num=0 %then %Do; proc report data = noobs; column x; define x / display ""; run; %End; %end; %if &ds=sashelp.class %then %do; ods excel options(autofilter="ALL" sheet_name="C"); proc report data=sashelp.class nowd split="~" missing style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box] ; column name ; define name /"name" ; run; %End; %Else %if &num=0 %then %Do; proc report data = noobs; column x; define x / display ""; run; %End; %end; %if &ds=sashelp.shoes %then %do; ods excel options(autofilter="ALL" sheet_name="d"); proc report data=sashelp.shoes nowd split="~" missing style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0]; %IF &num. > 0 %Then %Do; column region ; define region/ "region"; run; %End; %Else %Do; proc report data =noobs; column x; define x / display ""; run; %End; %end; %mend;/*%MACRO mm(ds); */ title; footnote; ods results off; ods listing close; ods escapechar="^"; ods excel file="C:\rpt.xlsx" style=excel options (row_repeat="header" frozen_headers="ON" frozen_rowheaders="OFF" sheet_interval="PROC" ); %mm2(sashelp.car); %mm2(sashelp.class); %mm2(sashelp.shoes); ods excel close; ods results on; ods listing;
I'm not sure what you gain by stuffing data set tests and then selecting the proc report code based on those names unless you have many sets with similar structures. Generally if I have multiple sets that need similar treatment, I would make a specific to that structure report macro and then call each specific macro with an appropriate data set.
I did not test any of the code above, partially because of that &num macro variable.
you are right, I am stuck with the overwriting part. I have to took the first ods part out of the macro and manipulate the other parts of the code.
BTW: By adding "Sheet_interval="PROC" did work nicely,
thanks all
title; footnote; ods results off; ods listing close; ods escapechar="^"; ods excel file="C:\rpt.xlsx" style=excel options (row_repeat="header" /*to output to different sheets*/ sheet_interval="PROC"); %macro mm2(ds); %if &ds=sashelp.cars %then %do; ods excel options (sheet_tname = "A"); proc report data=sashelp.cars nowd split='~' missing style(header)=[just=left] style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box]; column make ; define make /"make"; run; %end; %else %if &ds=sashelp.class %then %do; ods excel options(autofilter="ALL" sheet_name="C"); proc report data=sashelp.class nowd split="~" missing style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0 borderstyle=solid borderwidth=2pt frame=box] ; column name ; define name /"name" ; run; %end; %else %if &ds=sashelp.shoes %then %do; ods excel options(autofilter="ALL" sheet_name="d"); proc report data=sashelp.shoes nowd split="~" missing style(report)=[font_size=9pt rules=all bordercolor=#f0f0f0]; column region ; define region/ "region"; run; %end; /*%MACRO mm(ds); */ %mend; %mm2(sashelp.cars ); %mm2(sashelp.class ); %mm2(sashelp.shoes ); ods excel close; ods results on; ods listing;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: