how to concatenate proc reports into one excel file

[HitmonTran]

Hi,

I am creating a patient profile listing. Original program is output in PDF but now I have to convert it into excel, but when I use ods excel, it creates multiple sheets in the excel file instead of one sheet. Is there a way I can concatenate the proc reports?

original code in ods pdf:

%MACRO profile;
	%do i=1 %to &subj_n.;
		%let select_subj = %scan(&subj., &i., '#');  *Creating macro variables to select patients one by one;

	options;
		ods listing close;
		ods html close;
		ods escapechar='^';
		ods pdf file = "..\..\Patient Profile Subject &select_subj..pdf" startpage=no style= TLF_CN_9;
		option formchar="|----|+|---+=|-/\<>*" ls=70 ps=70 missing = " " nobyline nocenter nodate nonumber orientation=landscape;
		title; 
		title1 j=l "4" j=r "DNDI-Master";
		title2 j=l ")" j=r "Page ^{thispage} of ^{lastpage}";
		title3 j=c " ";
		title4 j=c "Patient Profile";
		title5;
		title6 j=l "Subject:&select_subj. 	";

		footnote;
		footnote1 j=l " ";
		footnote2 j=l " ";
		footnote3 j=l " ";
		footnote4 j=c "Patient Profile";
		footnote5 j=l "Program Name: &prname." j=c "Dataset Extraction Date: (&datextrt.)" j=r "Generation Date/Time: %sysfunc(today(), yymmdd10)/&systime.";

		*****	dm report	********;
		proc report data = raw.demographics (where=(sub_profile_id="&select_subj.")) split = "|" spacing = 3 missing nowindows headline headskip style(header)={textalign=l} spanrows;
			columns ("Dataset(s) : Demographics, Informedconsentconsenteme_2" sub_profile_id dsstdat version_id icprsubj age sex_decode race);
		
			define sub_profile_id /group left width = 38 "Subject ID";
			define dsstdat /display left width = 40 "Informed Consent Date|/dsstdat";
			define version_id /display left width = 40 "Protocol Version Number|/version_id";
			define icprsubj /display left width = 40 "Previous Subject Number|/icprsubj";
			define age /display left width = 40 "Age|/icprsubj";
			define sex_decode /display left width = 40 "Gender|/sex_decode";
			define race /display left width = 20 "Race|/multi vars";
		run;


       *****	Adverse events report	********;
		proc report data = raw.adverse_events (where=(sub_profile_id="&select_subj.")) split = "|" spacing = 3 missing nowindows headline headskip style(header)={textalign=l} spanrows;
			columns ("Dataset(s) : Adverse_events" aestdtc aeendtc interval_name_ aeterm aeongo_decode aesev_decode aefrq_decode);
					
			define aestdtc /display left width = 2 "Start Date|/aestdtc";
			define aeendtc /display left width = 2 "End Date|/aeendtc";
			define interval_name /display left width = 2 "interval Name|/interval_name";
			define aeterm /display left width = 2 "Adverse Event Term|/aeterm";
			define aeongo_decode /display left width = 20 "Ongoing?|/aeongo_decode";
			define aesev_decode /display left width = 2 "Severity|/aesev_decode";
			define aefrq_decode /display left width = 2 "Pattern|/aefrq_decode";
		run;

ods pdf close;

%end;
%mend profile;

[SASKiwi]

Try ODS option sheet_interval = "NONE".  

[HitmonTran]

Like this? 

	options;
		ods listing close;
		ods escapechar='^';
		ods tagsets.ExcelXP file="..\..\DM\MMPlisting_report\Patient Profile Subject &select_subj..xml" style=statistical;
		ODS option sheet_interval = "NONE";
	*	ods html close;
		
	*	ods pdf file = "..\..\ICON GPHS\Biostatistics - Documents\Clinical Trials\4418-0006 01-COV\DM\MMPlisting_report\Patient Profile Subject &select_subj..pdf" startpage=no style= TLF_CN_9;
		option formchar="|----|+|---+=|-/\<>*" ls=70 ps=70 missing = " " nobyline nocenter nodate nonumber orientation=landscape;
		title; 
		title1 j=l "4418-0006 01-COV (MASTER)" j=r "DNDI-Master";
		title2 j=l "Protocol No.: 01-COV (MASTER)" j=r "Page ^{thispage} of ^{lastpage}";
		title3 j=c " ";
		title4 j=c "Patient Profile";
		title5;
		title6 j=l "Subject:&select_subj. 	";

		footnote;
		footnote1 j=l " ";
		footnote2 j=l " ";
		footnote3 j=l " ";
		footnote4 j=c "Patient Profile - MMB Request";
		footnote5 j=l "Program Name: &prname." j=c "Dataset Extraction Date: (&datextrt.)" j=r "Generation Date/Time: %sysfunc(today(), yymmdd10)/&systime.";

		*****	dm report	********;
		ods tagsets.excelxp options(sheet_name="demographics");

		proc report data = dmlst.demographics (where=(sub_profile_id="&select_subj.")) split = "|" spacing = 3 missing nowindows headline headskip style(header)={textalign=l} spanrows;
			columns ("Dataset(s) : Demographics, Informedconsentconsenteme_2" sub_profile_id dsstdat version_id icprsubj age sex_decode race);
		
			define sub_profile_id /group left width = 38 "Subject ID";
			define dsstdat /display left width = 40 "Informed Consent Date|/dsstdat";
			define version_id /display left width = 40 "Protocol Version Number|/version_id";
			define icprsubj /display left width = 40 "Previous Subject Number|/icprsubj";
			define age /display left width = 40 "Age|/icprsubj";
			define sex_decode /display left width = 40 "Gender|/sex_decode";
			define race /display left width = 20 "Race|/multi vars";
		run;

[SASKiwi · Accepted solution]

ods tagsets.excelxp options(sheet_interval="NONE");

[HitmonTran]

awesome that worked, but now how do i create different sheet per subject? thanks!