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

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

HitmonTran — Wed, 04 Nov 2020 21:00:40 GMT

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;

Re: ods excel. create combine multiple proc reports in one sheet & create multiple sheet per su

ballardw — Wed, 04 Nov 2020 20:46:08 GMT

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.

Re: ods excel. create combine multiple proc reports in one sheet & create multiple sheet per su

HitmonTran — Wed, 04 Nov 2020 21:01:00 GMT

ok i reworded, does it make sense?

Re: ods excel. create combine multiple proc reports in one sheet & create multiple sheet per su

SASKiwi — Wed, 04 Nov 2020 22:13:42 GMT

@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;