Footnote does not appear in one of the sheet when using ODS Excel.

Ani_Abby · Posted 11-01-2019 12:05 AM

Hi,

I have problem. My situation,

I have 6 sheets in one worksheet.
First sheet display graph, table, title on top of the graph and 1 line footnote below the table
Second sheet has 3 proc report. Title on the first proc report and 3 line footnote after the third proc report.
Third sheet, 1 proc report with title and 3 line footnote.
Fourth until sixth sheet, each have proc report and title.

My problem, footnote only in my second sheet does not appear in worksheet but it appear in SAS result.

DATA WORK.RPT_TAB_CHART1;
	SET WORK.DATA_CHART;

	LENGTH
		ACHIEVEMENT_P 8
		TARGET_P 8
		DISPLAY_COLUMN $35.
	;

	LABEL
		AREA="Achievement (%)"
		ACHIEVEMENT_P="Achievement (%)"
		TARGET_P="Target (%)"
		DISPLAY_COLUMN="Achievement (%)"
	;

	FORMAT
		ACHIEVEMENT_P NLPCTN10.0
		TARGET_P NLPCTN10.0
		TARGET COMMA32.2
		ACHIEVEMENT COMMA32.2
	;

	DISPLAY_COLUMN=ORDER_NO || ". " || AREA;
	TARGET_P = TARGET / TARGET;
	ACHIEVEMENT_P = ACHIEVEMENT / TARGET;
RUN;

PROC TRANSPOSE DATA=WORK.RPT_TAB_CHART1
	OUT=WORK.TRNS_RPT
	NAME=Source
	PREFIX=COL
	LABEL=DISPLAY_COL
;
	ID ORDER_NO;
	idLABEL AREA;
	VAR TARGET ACHIEVEMENT ACHIEVEMENT_P;
	
RUN;

ods EXCEL file="&OUTPUT_PATH./Yearly_Rpt.xlsx"
	style=Daisy
;


options orientation=LANDSCAPE;
options papersize=A4;

ODS EXCEL
		OPTIONS(SHEET_INTERVAL='NONE'
		SHEET_NAME="Chart"
		SHEET_LABEL="Chart" 
		EMBEDDED_TITLES= 'ON'
		EMBED_TITLES_ONCE= 'ON'
		EMBEDDED_FOOTNOTES = 'ON'
		EMBED_FOOTNOTES_ONCE='ON'
	)
	noGTITLE
	NOGFOOTNOTE
;

ods graphics on / reset=all;
ods graphics on / width=100%;

DATA WORK.RPT_CHART_BAR;
	SET WORK.RPT_TAB_CHART1
	(KEEP=DISPLAY_COLUMN
		ACHIEVEMENT_P
		TARGET_P
	);

	LABEL
		DISPLAY_COLUMN=""
		ACHIEVEMENT_P="Achievement (%)"
		TARGET_P="Target (%)"
	;

RUN;

PROC SORT DATA=WORK.RPT_CHART_BAR;
	BY DISPLAY_COLUMN;
RUN;

TITLE1 HEIGHT=12pt BOLD J=CENTER "Achievement By Area (Actual vs Budget)";

PROC SGPLOT DATA=WORK.RPT_CHART_BAR 
	NOBORDER
	pad=(bottom=2%)
	nowall
;

	VBAR DISPLAY_COLUMN  / RESPONSE=ACHIEVEMENT_P
		DATALABEL
		DATALABELPOS=DATA
		FILL
		FILLTYPE=SOLID
		FILLATTRS=(COLOR=BLUE)
		STAT=SUM
		NOSTATLABEL	
		NOOUTLINE
		LEGENDLABEL="Achievement (%)"
		DATASKIN=NONE
		NOZEROBARS
		BARWIDTH=0.7
	;

	VLINE DISPLAY_COLUMN / RESPONSE=TARGET_P
		LINEATTRS=(COLOR=RED)
		NOSTATLABEL
		LEGENDLABEL="Target (%)"
		Y2AXIS
	;

	XAXIS DISPLAY=(NOLABEL) 
		VALUESROTATE=DIAGONAL2
		grid
		VALUEATTRS=(SIZE=8PT)
	;
	YAXIS 
		GRID 
		MIN=0
		GRIDATTRS=GraphAxisLines
		VALUES=(0 TO 1.4 BY 0.2)
	;

	Y2AXIS
		GRID
		DISPLAY=(NOLABEL NOVALUES)
		VALUES=(0 TO 1.4 BY 0.2)
	;
	
	
run;

PROC REPORT DATA=WORK.TRNS_RPT 
	style(report)=[cellspacing=1 cellpadding=1 FONTFAMILY="Calibri,Arial" FONTSIZE=9pt] split='*' center 
	style(header)=[cellpadding=1 cellspacing=1 FONTFAMILY="Calibri,Arial" FONTSTYLE=ROMAN FONTSIZE=7pt] 
	style(column)=[cellspacing=1 cellpadding=1 FONTFAMILY="Calibri,Arial" FONTSIZE=9pt] ;

	COLUMN
		SOURCE DISPLAY_COL 
		COL1 -  COL18
	;
		
	DEFINE SOURCE / NOPRINT;
	DEFINE DISPLAY_COL / "ENR By Sales Area*(RM'M)" ORDER=DATA MISSING STYLE={cellwidth=1.0in};
	DEFINE COL1 - COL18 / ORDER=DATA MISSING F=COMMA32.2 ;

	COMPUTE SOURCE;
		IF SOURCE = 'ACHIEVEMENT_P' THEN DO;
			call define(_ROW_, "style","style=[tagattr='format:##0%']");
		END;
	ENDCOMP;

	FOOTNOTE1 ITALIC J=LEFT "* Excluded oversea Branches";
RUN;
QUIT;

ods excel options(sheet_interval="table");
ods exclude all;
data _null_;
file print;
put _all_;
run;

ods select all;

ods EXCEL 

	options(
		SHEET_INTERVAL='NONE'
		sheet_name="Summary"
		SHEET_LABEL="Summary"
		EMBEDDED_FOOTNOTES='ON'
		EMBEDDED_TITLES= 'ON'
		EMBED_TITLES_ONCE= 'ON'
	)  box_sizing=CONTENT_BOX
;


options missing='-';

PROC REPORT DATA=WORK.RPT_TAB1 SPLIT="*"
	style(report)=[cellspacing=2 cellpadding=10] split='*' center
	style(header)=[FONTWEIGHT=BOLD cellpadding=30 cellspacing=20]
	style(column)=[cellspacing=2 cellpadding=5]
;
	COLUMN
		RPT_DESC
		PREV_YEAR
		PREV_MTH2
		PREV_MTH1
		MONTH_TARGET
		CUR_EOP
		LAST_EOP
		COMP_ACHIEVEMENT
		MTD_GROWTH
		MOM_GROWTH
		YTD_GROWTH
		LINE_BOLD
	;
	where RPT_MAIN_GRP = '1_ALL';


	DEFINE LINE_BOLD / NOPRINT;
	DEFINE RPT_DESC / "ENR ('000)" ORDER=DATA MISSING STYLE= {tagattr="type:String" cellwidth=2.1in};
	DEFINE PREV_YEAR / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.1in};
	DEFINE PREV_MTH2 / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.1in};
	DEFINE PREV_MTH1 / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.1in};
	DEFINE MONTH_TARGET / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.1in};
	DEFINE CUR_EOP / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.1in};
	DEFINE LAST_EOP / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.1in};
	DEFINE COMP_ACHIEVEMENT / COMPUTED format=NLPCTN10.1 "(E)/(D)*Achievement (%)" STYLE={cellwidth=1.1in};
	DEFINE MTD_GROWTH / STYLE={cellwidth=1.2in};
	DEFINE MOM_GROWTH / STYLE={cellwidth=1.2in};
	DEFINE YTD_GROWTH / STYLE={cellwidth=1.2in};

	COMPUTE COMP_ACHIEVEMENT;
		IF _C5_ <> 0 THEN COMP_ACHIEVEMENT = _C6_ / _C5_;
		ELSE COMP_ACHIEVEMENT = 0;
	ENDCOMP;

	COMPUTE LINE_BOLD;
		IF LINE_BOLD = 'Y' THEN
		call define(_ROW_, "style",
                  "style=[fontweight=bold]");
	ENDCOMP;

	TITLE1 J=LEFT BOLD UNDERLIN=1 HEIGHT=12pt "Sales Growth By Product By Area As At: &REPORT_DATE.";
RUN;



PROC REPORT DATA=WORK.RPT_TAB1 SPLIT="*" NOHEADER
	style(report)=[cellspacing=2 cellpadding=10] split='*' center
	style(column)=[cellspacing=2 cellpadding=5];
	COLUMN
		RPT_DESC
		PREV_YEAR
		PREV_MTH2
		PREV_MTH1
		MONTH_TARGET
		CUR_EOP
		LAST_EOP
		COMP_ACHIEVEMENT
		MTD_GROWTH
		MOM_GROWTH
		YTD_GROWTH
		LINE_BOLD
	;

	where RPT_MAIN_GRP <> '1_ALL';


	DEFINE LINE_BOLD / NOPRINT;
	DEFINE RPT_DESC / "('000)" ORDER=DATA MISSING STYLE= {tagattr="type:String" cellwidth=2.2in};
	DEFINE PREV_YEAR / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE PREV_MTH2 / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE PREV_MTH1 / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE MONTH_TARGET / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE CUR_EOP / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE LAST_EOP / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE COMP_ACHIEVEMENT / COMPUTED format=NLPCTN10.1 "(E)/(D)*Achievement (%)" STYLE={cellwidth=1.2in};
	DEFINE MTD_GROWTH / STYLE={cellwidth=1.2in};
	DEFINE MOM_GROWTH / STYLE={cellwidth=1.2in};
	DEFINE YTD_GROWTH / STYLE={cellwidth=1.2in};

	COMPUTE COMP_ACHIEVEMENT;
		IF _C5_ <> 0 THEN COMP_ACHIEVEMENT = _C6_ / _C5_;
		ELSE COMP_ACHIEVEMENT = 0;
	ENDCOMP;

	COMPUTE LINE_BOLD;
		IF LINE_BOLD = 'Y' THEN DO;
			call define(_ROW_, "style","style=[fontweight=bold]");
		END;
	ENDCOMP;

	compute after;
		line ' ';
	endcomp;


RUN;

PROC REPORT DATA=WORK.RPT_TAB1B SPLIT="*"
	style(report)=[cellspacing=2 cellpadding=10] split='*' center
	style(header)=[FONTWEIGHT=BOLD cellpadding=30 cellspacing=20]
	style(column)=[cellspacing=2 cellpadding=5]
;
	COLUMN
		AREA
		PREV_YEAR
		PREV_MTH2
		PREV_MTH1
		MONTH_TARGET
		CUR_EOP
		LAST_EOP
		COMP_ACHIEVEMENT
		MTD_GROWTH
		MOM_GROWTH
		YTD_GROWTH
	;


	DEFINE AREA / "By Area ('000)" ORDER=DATA MISSING STYLE= {tagattr="type:String" cellwidth=2.2in};
	DEFINE PREV_YEAR / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE PREV_MTH2 / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE PREV_MTH1 / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE MONTH_TARGET / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE CUR_EOP / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE LAST_EOP / ORDER=DATA MISSING style={tagattr="format:#,##0.00" cellwidth=1.2in};
	DEFINE COMP_ACHIEVEMENT / COMPUTED format=NLPCTN10.1 "(E)/(D)*Achievement (%)" STYLE={cellwidth=1.2in};
	DEFINE MTD_GROWTH / STYLE={cellwidth=1.2in};
	DEFINE MOM_GROWTH / STYLE={cellwidth=1.2in};
	DEFINE YTD_GROWTH / STYLE={cellwidth=1.2in};

	COMPUTE COMP_ACHIEVEMENT;
		IF _C5_ <> 0 THEN COMP_ACHIEVEMENT = _C6_ / _C5_;
		ELSE COMP_ACHIEVEMENT = 0;
	ENDCOMP;

FOOTNOTE1 ITALIC J=LEFT "* footnote line 1";
FOOTNOTE2 ITALIC J=LEFT "* footnote line 2";
FOOTNOTE3 ITALIC J=LEFT "* footnote line 3";
	
RUN;



QUIT;

ods EXCEL 

	options(
		SHEET_INTERVAL='PROC'
		sheet_name="By Branches"
		SHEET_LABEL="By Branches"
		EMBEDDED_TITLES= 'ON'
		EMBEDDED_FOOTNOTES = 'ON'
	)
;

options missing="-";

PROC REPORT DATA=WORK.RPT_TAB2 SPLIT="*"
	style(report)=[cellspacing=2 cellpadding=10] split='*' center
	style(header)=[FONTWEIGHT=BOLD cellpadding=30 cellspacing=20]
	style(column)=[cellspacing=2 cellpadding=5]
;

COLUMN
		AREA
		FOR_THE_YEAR
		MTH1 - MTH12
		ACHIEVEMENT
		TARGET
		;

TITLE;
TITLE1 J=LEFT BOLD UNDERLIN=1 HEIGHT=12pt "Portfolio Sales Growth By Area As At: &REPORT_DATE.";
TITLE3;


FOOTNOTE;
FOOTNOTE1 ITALIC J=LEFT "* footnote line 1";
FOOTNOTE2 ITALIC J=LEFT "* footnote line 2";
FOOTNOTE3 ITALIC J=LEFT "* footnote line 3";

RUN;
QUIT;

proc report data=WORK.RPT_AV_C nowd
	style(report)=[cellspacing=2 cellpadding=10] split='*' center
	style(header)=[FONTWEIGHT=BOLD cellpadding=30 cellspacing=20]
	style(column)=[cellspacing=2 cellpadding=5]
;
	column 	
			EFFECTIVE_DATE
			BRANCH
			ACCOUNT
			CLOSING_BALANCE
			AREA
			PRODUCT
	;
		
	TITLE;
	TITLE1 J=LEFT BOLD HEIGHT=12pt "AVC Portfolio Sales Listing As At: &REPORT_DATE.";
	TITLE3;

	FOOTNOTE;

RUN;
quit;

	ods EXCEL 
	options(sheet_interval='PROC' 
	sheet_name="AFI"
	SHEET_LABEL="AFI"
	EMBEDDED_FOOTNOTES='OFF'
	EMBED_FOOTNOTES_ONCE='OFF'
);

options missing='';

proc report data=WORK.RPT_AV_I nowd
	style(report)=[cellspacing=2 cellpadding=10] split='*' center
	style(header)=[FONTWEIGHT=BOLD cellpadding=30 cellspacing=20]
	style(column)=[cellspacing=2 cellpadding=5]
;

	column 	
			EFFECTIVE_DATE
			BRANCH
			ACCOUNT
			CLOSING_BALANCE
			AREA
			PRODUCT
	;
		
	TITLE;
	TITLE1 J=LEFT BOLD HEIGHT=12pt "AVI Portfolio Sales Listing As At: &REPORT_DATE.";
	TITLE3;

	FOOTNOTE;

RUN;
quit;

	ods EXCEL 
	options(sheet_interval='PROC' 
	sheet_name="Floor"
	SHEET_LABEL="Floor"
	EMBEDDED_FOOTNOTES='OFF'
	EMBED_FOOTNOTES_ONCE='OFF'
);

options missing='';

proc report data=WORK.RPT_FLOOR nowd
	style(report)=[cellspacing=2 cellpadding=10] split='*' center
	style(header)=[FONTWEIGHT=BOLD cellpadding=30 cellspacing=20]
	style(column)=[cellspacing=2 cellpadding=5]
;

	column 	
			EFFECTIVE_DATE
			BRANCH
			ACCOUNT
			CLOSING_BALANCE
			AREA
			PRODUCT
	;
		
	
	TITLE;
	TITLE1 J=LEFT BOLD HEIGHT=12pt "Floor Portfolio Sales Listing As At: &REPORT_DATE.";
	TITLE3;

	FOOTNOTE;
RUN;
quit;

ods EXCEL close;

Reeza · Posted 11-01-2019 04:13 AM

You didn’t post your code so it’s hard to say but I’d guess you didn’t use footnote correctly, most likely you reused an index and it was overwritten.
Or it didn’t get executed at the correct time based on the order of your code.

I’m also assuming you mean 6 sheets in one workbook.

@Ani_Abby wrote:

Hi,

I have problem. My situation,

I have 6 sheets in one worksheet.

First sheet display graph, table, title on top of the graph and 1 line footnote below the table

Second sheet has 3 proc report. Title on the first proc report and 3 line footnote after the third proc report.

Third sheet, 1 proc report with title and 3 line footnote.

Fourth until sixth sheet, each have proc report and title.

My problem, footnote only in my second sheet does not appear in worksheet but it appear in SAS result.

Ani_Abby · Posted 11-01-2019 05:52 AM

I've edited my post to include the codes 🙂

Footnote does not appear in one of the sheet when using ODS Excel.

Re: Footnote does not appear in one of the sheet when using ODS Excel.

Re: Footnote does not appear in one of the sheet when using ODS Excel.