ods excel. create combine multiple proc reports in one sheet & create multiple sheet per subject

[HitmonTran]

Hi,

I have a program with multiple proc reports and need to create one excel workbook that is concatenating all proc reports per subject per sheet.  I'm either able to output one sheet containing all subjects and all proc reports OR one sheet per subject with ONE proc report.  I believe my ods option (sheet_interval="NONE" & "sheet_name="&select_subj.") is interfering with each other. Thanks!

MACRO ptpf;

	 ods listing close;
      ods escapechar='^';
      ods excel options(sheet_interval="NONE") file="excel.xls";

	%do i=1  %to &subj_n.;
		%let select_subj = %scan(&subj., &i., '#');  *Creating macro variables to select patients one by one;
	
		options;
		ods excel options(sheet_name="&select_subj.") ;

        /*****PROC REPORT******/
        proc report data =a;
        run;

        proc report data =b;
        run;

       proc report data =c;
       run;

     %end;

ods excel close;
%mend ptpf;

[ballardw]

Please rewrite this sentence fragment so that it makes sense:

concatenating all proc reports in one sheet per subject while having multiple sheet per subject.

"one sheet per subject" and "multiple sheet per subject" does not make sense.

If one of these is "one sheet per topic" and "multiple sheets per person" or similar then say so. But using the same word "subject" may be obscuring what you intend.

Best might be to manually create an example work book that demonstrates what you want.

[HitmonTran]

ok i reworded, does it make sense?

[SASKiwi]

@HitmonTran  - Here is a test program that I know works for doing multiple sheets in the same workbook. You should be able to run this yourself. You can then apply the same approach to your own code.

ods excel file = "Test1.xls";

ods excel options(sheet_interval="NONE" sheet_name="Males");

proc print data= sashelp.class;
  where sex = 'M';
run;

ods excel options(sheet_interval="NONE" sheet_name="Females");

proc print data= sashelp.class;
  where sex = 'F';
run;

ods excel close;