topic Re: How to create multiple sheets in excel for each subjects (patient profile listing) in SAS Programming

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

HitmonTran — Tue, 03 Nov 2020 23:08:33 GMT

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;

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

SASKiwi — Tue, 03 Nov 2020 23:47:11 GMT

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.

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

HitmonTran — Wed, 04 Nov 2020 02:34:25 GMT

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;

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

SASKiwi — Wed, 04 Nov 2020 02:55:53 GMT

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

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

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

it's only outputting one subject

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

SASKiwi — Wed, 04 Nov 2020 03:38:35 GMT

You need a changing sheet name. Try this then:

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

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

HitmonTran — Wed, 04 Nov 2020 04:00:05 GMT

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;

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

SASKiwi — Wed, 04 Nov 2020 06:19:15 GMT

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.

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

HitmonTran — Wed, 04 Nov 2020 20:16:09 GMT

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!

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

Kurt_Bremser — Wed, 04 Nov 2020 20:52:25 GMT

Try

sheet_interval="group"

and run the PROC REPORT without the WHERE.

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

HitmonTran — Wed, 04 Nov 2020 21:04:03 GMT

unfortunately it didn't work

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

SASKiwi — Wed, 04 Nov 2020 22:36:14 GMT

See my example in your new post.

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

HitmonTran — Wed, 04 Nov 2020 22:50:53 GMT

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

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

Tom — Wed, 04 Nov 2020 23:44:21 GMT

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.

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

Tom — Wed, 04 Nov 2020 23:46:20 GMT

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.

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

SASKiwi — Wed, 04 Nov 2020 23:57:12 GMT

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;

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

HitmonTran — Thu, 05 Nov 2020 02:30:51 GMT

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)

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

SASKiwi — Thu, 05 Nov 2020 19:08:09 GMT

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

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

HitmonTran — Thu, 05 Nov 2020 19:40:52 GMT

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.

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

SASKiwi — Thu, 05 Nov 2020 23:34:08 GMT

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