How to create multiple sheets in excel for each subjects (patient profile listing)

[HitmonTran]

Yes, I want to stack Demog, AE, along with other domains, exactly  like it is done in pdf/rtf.

So the final result with give me to one workbook  with one sheet for ALL domains per subject. 

it doesn't look bad when I use ods excel options(sheet_interval="NONE") ; (see screenshot of output)

[SASKiwi]

@HitmonTran - Did you miss my earlier reply using SHEET_INTERVAL = NOW? Try my test program. If it works for you then use the same approach in your macro.

[HitmonTran]

Yes I saw but it didn't work. The team and I came to the conclusion that we might just need to create multiple workbooks per subject instead.

[SASKiwi]

@HitmonTran  - This is what I get when running my test program - a single workbook with two tabs, one for Males and one for Females. This is what I thought you wanted - being able to split reports between tabs? If my understanding is incorrect then what do you actually want?

[HitmonTran]

Yes that is correct. However when I want to output multiple proc reports with different datasets within the same sheet it starts to get complicated.

[Kurt_Bremser]

With option sheet_interval="none", all output will go to the same sheet until you change the option.

[SASKiwi]

@HitmonTran - I understand your frustration. I finally got a test working with multiple sheets AND reports. The secret is to use an interval of NONE on the first sheet (since you don't need a new sheet yet) and use NOW to switch to the next sheet:

ods excel file = "Test1.xlsx";

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

proc print data= sashelp.class;
  where sex = 'M' and age >= 13;
run;

ods excel options(sheet_interval="NONE");

proc print data= sashelp.class;
  where sex = 'M' and age < 13;
run;

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

proc print data= sashelp.class;
  where sex = 'F' and age >= 13;
run;

ods excel options(sheet_interval="NONE");

proc print data= sashelp.class;
  where sex = 'F' and age < 13;
run;

ods excel close;

[Tom]

Before make the macro get the logic to work first with hard coded values.

Pick one or two subjects.

Code the steps you want and see if you can get the layout you want.

Then try to use macro code the generate the code.

[Preethi995]

Using this file for creating multiple sheets in excel for each subjects

%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 tagsets.excelxp options(sheet_interval="NONE") file="Patient Profile Subject &select_subj..xls";
		option formchar="|----|+|---+=|-/\<>*" ls=70 ps=70 missing = " " nobyline nocenter nodate nonumber orientation=landscape;
		title; 
		title1 j=l "4" j=r "DNDI-Master";
	 
		footnote;
		footnote1 j=l " ";
		 
		*****	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;

 

Here it follows https://www.tutorsbot.com/course/DevOps-Training