topic I am using ods excel to export two tables into one sheet%2C but the footnotes keep repeating in SAS Programming

I am using ods excel to export two tables into one sheet%2C but the footnotes keep repeating

bobeng — Tue, 25 May 2021 16:36:45 GMT

Re: I am using ods excel to export two tables into one sheet%2C but the footnotes keep repeating

Reeza — Tue, 25 May 2021 16:45:06 GMT

Show your code and log.

Re: I am using ods excel to export two tables into one sheet%2C but the footnotes keep repeating

bobeng — Tue, 25 May 2021 17:05:12 GMT

The code:

%macro psubpop;
ODS TAGSETS.EXCELXP file="T:\Folder\Outcomes 2019 &region..xml "
style=Htmlblue ;
ods tagsets.ExcelXP options
(embedded_titles='yes' embedded_footnotes='yes' ORIENTATION='Landscape' autofit_height='yes' sheet_interval='none' sheet_name="700");;

PROC REPORT DATA=All_Sti3 nowd headline headskip;
COLUMN outcomes outcome upstate &region. ;
define outcomes/display ' ' order style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.8in cellheight=30};
define outcome/display 'Outcome' order order=DATA style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=4in cellheight=30};
define upstate/display 'UPSTATE' style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.2in cellheight=30};
define &region./display "&region." style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.4in cellheight=30};
title1 height=10pt bold justify=left "Outcomes ~(super 1) January-December 2019 ";
where DIAGNOSIS='ES' and pop='M';
RUN;

PROC REPORT DATA=All_Sti3 nowd headline headskip;
COLUMN outcomes outcome upstate &region. ;
define outcomes/display ' ' group style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.8in cellheight=30};
define outcome/display 'Outcome' order order=DATA style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=4in cellheight=30};
define upstate/display 'UPSTATE' style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.2in cellheight=30};
define &region./display "&region." style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.4in cellheight=30};
title1 height=10pt bold justify=left "Outcomes2 ~{super 8} January-December 2019";
where DIAGNOSIS='ES' and pop='F';
footnote1 height=8pt justify=left
"~{super 1}OP's ";
footnote2 height=8pt justify=left
"~{super 2}Number and percent of case assignments are based on report year ";
footnote3 height=8pt justify=left
"~{super 3}Testing ";
footnote4 height=8pt justify=left
"~{super 4}PrEP:Referral are calculated by excluding Current ";
RUN;
ods tagsets.excelxp close;

%mend;
%psubpop;

Re: I am using ods excel to export two tables into one sheet%2C but the footnotes keep repeating

Reeza — Tue, 25 May 2021 17:08:08 GMT

Add a footnote statement before the next PROC REPORT to reset your FOOTNOTES. Note that footnotes come after the proc but need to be assigned before to have them available for usage. They also don't belong in the PROC REPORT.

Re: I am using ods excel to export two tables into one sheet%2C but the footnotes keep repeating

Reeza — Tue, 25 May 2021 17:09:37 GMT

%macro psubpop;
ODS TAGSETS.EXCELXP file="T:\Folder\Outcomes 2019 &region..xml "
style=Htmlblue ;

ods tagsets.ExcelXP options
(embedded_titles='yes' embedded_footnotes='yes' ORIENTATION='Landscape' autofit_height='yes' sheet_interval='none' sheet_name="700");;

footnote;
title1 height=10pt bold justify=left "Outcomes ~(super 1) January-December 2019 ";

PROC REPORT DATA=All_Sti3 nowd headline headskip;
where DIAGNOSIS='ES' and pop='M';
COLUMN outcomes outcome upstate &region. ;
define outcomes/display ' ' order style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.8in cellheight=30};
define outcome/display 'Outcome' order order=DATA style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=4in cellheight=30};
define upstate/display 'UPSTATE' style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.2in cellheight=30};
define &region./display "&region." style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.4in cellheight=30};

RUN;



footnote1 height=8pt justify=left
"~{super 1}OP's  ";
footnote2 height=8pt justify=left
"~{super 2}Number and percent of case assignments are based on report year ";
footnote3 height=8pt justify=left
"~{super 3}Testing ";
footnote4 height=8pt justify=left
"~{super 4}PrEP:Referral are calculated by excluding Current ";

PROC REPORT DATA=All_Sti3 nowd headline headskip;
where DIAGNOSIS='ES' and pop='F';
COLUMN outcomes outcome upstate &region. ;
define outcomes/display ' ' group style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.8in cellheight=30};
define outcome/display 'Outcome' order order=DATA style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=4in cellheight=30};
define upstate/display 'UPSTATE' style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.2in cellheight=30};
define &region./display "&region." style(HEADER)={just=center font_weight=bold fontsize=2.8 vjust=bottom cellwidth=1.4in cellheight=30};
title1 height=10pt bold justify=left "Outcomes2 ~{super 8} January-December 2019";

RUN;
ods tagsets.excelxp close;

%mend;
%psubpop;

Something like this should work for you.