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

[HitmonTran]

Hello,

I need to create a sheet per subject in excel for my patient profile listings.

So far I was able to create one sheet for all domains in one sheet per subject, but now i need to create multiple sheets per subject. I'm afraid this statement might prevent it from happening  ods tagsets.excelxp options(sheet_interval="NONE")

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

 
%end;
%mend profile;

[SASKiwi]

For each subject add this - Subject1 could be a macro variable:

ods tagsets.excelxp options(sheet_interval="NONE" sheet_name="Subject1")

You should move the ODS statement creating the spreadsheet file before the start of the loop as you only need  to do that once - assuming you only want one workbook.

[HitmonTran]

Not sure if I'm doing this correctly bc it's not working. Below is my code, thank you: 

proc sql;
	select unique(sub_profile_id) into : subj /*separated by "#"*/ from raw.demographics; 
	select count(sub_profile_id) into : subj_n from raw.demographics;  
quit;

%put &subj &subj_n;

%MACRO profile;
      ods listing close;
         ods escapechar='^';
         ods tagsets.excelxp options(sheet_interval="NONE" sheet_name="&subj.") file="..\..\DM\Patient Profile Subject.xls";
	%do i=1 %to &subj_n.;
		%let select_subj = %scan(&subj., &i., '#');  *Creating macro variables to select patients one by one;

	options;
	
 
		option formchar="|----|+|---+=|-/\<>*" ls=70 ps=70 missing = " " nobyline nocenter nodate nonumber orientation=landscape;
		title; 
		title1 j=l "4" j=r "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;

 
%end;
%mend profile;

[SASKiwi]

%MACRO profile;
      ods listing close;
         ods escapechar='^';
         ods tagsets.excelxp file="..\..\DM\Patient Profile Subject.xls";
	%do i=1 %to &subj_n.;
		%let select_subj = %scan(&subj., &i., '#');  *Creating macro variables to select patients one by one;

	options;
	
         ods tagsets.excelxp options(sheet_interval="NONE" sheet_name="&subj.");
 
		option formchar="|----|+|---+=|-/\<>*" ls=70 ps=70 missing = " " nobyline nocenter nodate nonumber orientation=landscape;
		title; 
		title1 j=l "4" j=r "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;

 
%end;
%mend profile;

[HitmonTran]

it's only outputting one subject

[SASKiwi]

You need a changing sheet name. Try this then:

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

[HitmonTran]

Didn't work. Do you think it's the proc sql or proc report step? this is what it displays in the log for proc report. not sure what the previous programmer add "separated by '#'", but if that's not entered then the log will only display only one subject

11436   proc sql;
11437       select unique(sub_profile_id) into : subj separated by "#" from dmlst.demographics;
11437 !                                                                                         /*dndiraw.demographics;
11437 ! */
11438       select count(sub_profile_id) into : subj_n from dmlst.demographics;
11438 !                                                                         /*dndiraw.demographics;*/
11439   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.04 seconds


11440
11441   %put &subj &subj_n;
01-COV-TEST10-ePRO#01-COV-TEST11-ePRO#01-COV-TEST12-ePRO#01-COV-TEST13-ePRO#01-COV-TEST14-ePRO#01-COV-TEST15-ePRO#01-COV
-TEST2-ePRO#01-COV-TEST3-ePRO#01-COV-TEST4-ePRO#01-COV-TEST5-ePRO#01-COV-TEST6-ePRO#01-COV-TEST7-ePRO#01-COV-TEST8-ePRO#
01-COV-TEST9-ePRO#01-COVDC100004#01-COVDC100014#01-COVDC100015#01-COVDC100016#01COVDC100001#01COVEG100001#01COVKE100001#
AndrewTest#AndrewTest10#AndrewTest3#AndrewTest5#AndrewTest6#AndrewTest8#COVIDTEST#Dan_language
test#Grace3_Language#Laurie_English
Dynamics#Laurie_TEST_ENGLISH#Laurie_Visits#Mind725aTUE#Mindy10#Mindy12#Mindy25Aug#Mindy825#Mindy9#MindyEPRO#MindyFriday#
MindyTuesday#MindyTuesday2#TEST123#TEST865#TEST987#TESTING2020#TESTPATIENT#TESTTEST#kathy10#kathy100#kathy12#kathy4#mind
y11#mindy6#mindy7#mindyTHUENG       57

proc sql;
	select unique(sub_profile_id) into : subj separated by "#" from dmlst.demographics; /*dndiraw.demographics;*/
	select count(sub_profile_id) into : subj_n from dmlst.demographics; /*dndiraw.demographics;*/
quit;

%put &subj &subj_n;

/*********************************************************************************/
/*****	Creating macro ptpf to generate each patient profile report.	******;*/
/*******************************************************************************;*/
%MACRO ptpt;

		 ods listing close;
         ods escapechar='^';
         ods excel file="..\..\DM\Patient Profile Subject.xls";


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


		************************************************************************************************************************
		**********************************	Generating pdf reports with patients profiles	************************************
		************************************************************************************************************************;
		options;
		 
		ods excel options(sheet_interval="NONE" sheet_name="&select_subj.") ;

option formchar="|----|+|---+=|-/\<>*" ls=70 ps=70 missing = " " nobyline nocenter nodate nonumber orientation=landscape;


	*****	dm report	********;
		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]

What does "didn't work" mean? Being more descriptive would be helpful. OK, let's try something simpler then:

* First ODS;
ods excel options(sheet_interval="NONE") file="..\..\DM\Patient Profile Subject.xls";
* Second ODS;
ods excel options(sheet_name="Sheet&i.") ;

That means you should end up with a series of sheets labelled sheet1, sheet2 and so on.

[HitmonTran]

Ok I am able to have all subjects in one workbook but it's all in one sheet. If i comment out "(sheet_interval="NONE")" i'm able to create multiple sheet per subject but then it would go back to step 1 where it is only displaying one Proc Report.  Any suggestion would help, thank you!

MACRO ptpf;

	 ods listing close;
     ods escapechar='^';
	* First ODS;
      ods excel options(sheet_interval="NONE") file="..\..\DM\MMPlisting_report\Patient Profile Subject.xls";

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


		************************************************************************************************************************
		**********************************	Generating pdf reports with patients profiles	************************************
		************************************************************************************************************************;
		options;
	
		* Second ODS;
		ods excel options(sheet_name="Sheet&i.") ;


***** 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 );

define sub_profile_id /group left width = 38 "Subject ID";
define dsstdat /display left width = 40 "Informed Consent Date|/dsstdat";
 
run;






%end;
ods excel close;
%mend ptpf;

Thank you!

[Kurt_Bremser]

Try

sheet_interval="group"

and run the PROC REPORT without the WHERE.

[HitmonTran]

unfortunately it didn't work

[SASKiwi]

See my example in your new post.

[HitmonTran]

unfortunately still doesn't work. seems like the options are overriding each other, maybe bc of the do loop or the multiple proc reports

[SASKiwi]

Sorry, but I've finally realised that the EXCEL destination works differently to the EXCELXP tagset. This works fine for me using the sheet interval NOW, not NONE.

ods excel file = "Test1.xlsx";

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

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

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

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

ods excel close;

[Tom]

I don't understand how you want to organize the workbook(s).

You said one sheet per subject, but you are printing different data domains (DEMOG, AE, etc).

Did you mean one workbook per subject with one sheet per data domain?

Or did you mean multiple sheets per subject?

Are you trying to stack DEMOG and AE into the same sheet? That could look pretty ugly.