The SAS Output Delivery System and reporting techniques

ODS Text= Appearing on New Sheet

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

ODS Text= Appearing on New Sheet

Hello,

 

I am creating an Excel spreadsheet with the ODS EXCEL destination. I am having trouble with getting my ODS TEXT to appear at the top of the second sheet instead of at the bottom of the first. 

 

Here is the structure of the code:

 

ods excel file="&path\report.xlsx";
ods excel options(sheet_name="Sheet 1");
ods escapechar="^";

proc sgplot data=...

ods excel options(sheet_name="Sheet 2");
ods text="^{style[font_size=8pt] Sheet 2 Info}";

proc report data=...

With this code, the ODS text "Sheet 2 Info" appears at the bottom of Sheet 1 instead of at the top of Sheet 2. 

 

Any advice on how to correct would be greatly appreciated. Thank you.

 

I am running on SAS EG with SAS 9.4 TS Level1M2.


Accepted Solutions
Solution
‎02-22-2018 01:12 PM
SAS Super FREQ
Posts: 9,423

Re: ODS Text= Appearing on New Sheet

Posted in reply to dhrumil_patel

Hi:
  I found this previous posting https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-excel-amp-multiple-sheets/td-p/261953 that seems relevant to your issue. When I try your code, in 9.4 M3 version, I only get one Worksheet. It seems to me from your code that you want to get 2 worksheets in your workbook.
 
The post in the Forums doesn't use ODS TEXT -- it just shows how to force a new sheet after you have used sheet_interval='none', so if you try their solution and use ODS TEXT, you might have to work with Tech Support if their solution doesn't work for you.
cynthia

View solution in original post


All Replies
SAS Super FREQ
Posts: 9,423

Re: ODS Text= Appearing on New Sheet

Posted in reply to dhrumil_patel
Hi: Just curious...you can use a TITLE statement with embedded_titles='yes' and reliably get the TITLE statement at the top of every sheet. Why do you want/need to use ODS TEXT?
cynthia
Contributor
Posts: 28

Re: ODS Text= Appearing on New Sheet

Posted in reply to Cynthia_sas

Hi Cynthia,

 

Thanks for your reply. 

 

The reason I'm using ODS TEXT is because the first output on each sheet is a SGPLOT with the gtitle option turned on (so the title appears in the plot). I'd like to have the title in the plot for the graph and a general title for the whole worksheet using ODS TEXT. I hope this clarifies. 

 

Dhrumil

SAS Super FREQ
Posts: 9,423

Re: ODS Text= Appearing on New Sheet

[ Edited ]
Posted in reply to dhrumil_patel

Hi:
From your posted code, it's not clear whether you have TITLE statements for each procedure. Is there a TITLE statement in the PROC REPORT or do you ONLY have ODS TEXT?

I don't understand what you mean by a "general title for the whole worksheet" -- do you mean the PROC REPORT worksheet has a "general title" -- a TITLE statement will give you that title on the second worksheet. I don't see any ODS TEXT for the first worksheet, so I am having a hard time envisioning where you want a "general" title -- on the first worksheet with the SGPLOT or on the second worksheet with the PROC REPORT output. Do you have more code to post instead of snippets? Are you using NOGTITLE or GTITLE as an option with ODS EXCEL for the SGPLOT? GTITLE will put your TITLE inside the graph image and NOGTITLE will put your TITLE outside the graph image, but you'll need the embedded_titles='yes' suboption for the title to appear in the workbook/worksheets.

cynthia

 

For example, see how NOGTITLE put the SGPLOT title at the top of the first worksheet when NOGTITLE is used. If you change the option to GTITLE, then the first sheet will have the title inside the graph image, but the second title will still be outside the graph image.

 

ods_excel_title.png

Contributor
Posts: 28

Re: ODS Text= Appearing on New Sheet

Posted in reply to Cynthia_sas

Hi Cynthia,

 

Sorry for the confusion; I see the code sample I posted was not accurate (with the PROC REPORT) and missing some details.

 

Let me provide a fuller version below. Also, I'm attaching a picture of the header of the first sheet. This shows the ODS TEXT "Model Performance" appearing as an overall title for the worksheet and the tile "Error Comparison" appearing in the SGPLOT graph. (Sorry for not including the whole graph; I don't believe I'm allowed to include the actual data).

 

So, in summary, I'm using GTITLE to put the titles in the SGPLOT graphs and using ODS TEXT to place text above the plots to act as additional "titles". 

 

I hope this clarifies the issue I'm trying to solve. Thank you.

 

 

 

ods _all_ close;
ods excel file="&path\Report.xlsx" style=SasWeb;
ods excel options(
sheet_name="Model Performance"
embedded_titles='yes'
embedded_footnotes='on'
sheet_interval='none') 
gtitle
gfootnote;

ods escapechar='^';

ods text='^{style[font_size=14pt font_weight=bold font_style=italic]
					Model Performance}';
ods text='                  ';

title "Error Comparison";
proc sgplot data=monthly_error;
	vline holdout_period / response=model_ape nostatlabel
												 lineattrs=(thickness=2);
	vline holdout_period / response=published_ape nostatlabel
												 lineattrs=(thickness=2);
	vbar holdout_period / response=N y2axis transparency=0.5 nostatlabel nooutline;
	xaxistable model_ape published_ape;
	xaxis display=(nolabel);
	yaxis display=(nolabel) grid offsetmin=0 values=(0 to 0.15 by 0.02);
	y2axis display=(nolabel);
	keylegend / noborder location=inside position=topright;
run;
title;

title "Jan 2018";
title2 h=10pt "Model Performance by Segment";
proc report data=error_metrics_model;
column segment model_ape model_error_pct_pt_change model_bias model_bias_pct_pt_change;
define segment / "Segment";
define model_error_pct_pt_change / style={foreground=delta_color.
																					font_weight=delta_weight.};
define model_bias_pct_pt_change / style={foreground=delta_color.
																					font_weight=delta_weight.};
run;
title;

title h=10pt "Published Performance by Segment";
proc report data=error_metrics_published;
column segment published_ape pub_error_pct_pt_change published_bias pub_bias_pct_pt_change;
define segment / "Segment";
define pub_error_pct_pt_change / style={foreground=delta_color.
																					font_weight=delta_weight.};
define pub_bias_pct_pt_change / style={foreground=delta_color.
																					font_weight=delta_weight.};
run;
title;

ods excel options(
sheet_name="Forecast Acceptance");

ods text='^{style[font_size=14pt font_weight=bold font_style=italic]
					Portugal Listing Forecast Acceptance}';
ods text='                  ';
ods text='^{style[font_size=10pt]*/
					Acceptance Rate by Imputation}';
ods text='^{style[font_size=8pt font_style=italic]
					Lead Trims Only}';

footnote;
footnote j=right italic height=8pt "Lead Trims Only";
footnote2 j=right italic height=8pt "Acceptance: +/- 1%";
title "Acceptance Rate by Imputation";
proc sgplot data=acceptance_rate_trend noborder;
	vbar published_period / response=acceptance_rate group=imputation nostatlabel
													groupdisplay=cluster nooutline;
	xaxis display=(nolabel);
	yaxis grid;
	keylegend / location=inside position=topleft noborder;
run;
title;
footnote;

title "Top Value Curves with Greatest Average Delta from Forecast";
title2 height=10pt "For Value Curves with More Than 5 Count";
proc report data=pricecurve_ranked(where=(Rank<=20));
column Rank Segment_vims Pricingcurve lead_trims avg_delta;
define rank / "Rank";
define segment_vims / "Segment";
define pricingcurve / "Value Curve";
run;
title;

ods excel close;

 Capture.PNG

 

 

 

 

 

Solution
‎02-22-2018 01:12 PM
SAS Super FREQ
Posts: 9,423

Re: ODS Text= Appearing on New Sheet

Posted in reply to dhrumil_patel

Hi:
  I found this previous posting https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-excel-amp-multiple-sheets/td-p/261953 that seems relevant to your issue. When I try your code, in 9.4 M3 version, I only get one Worksheet. It seems to me from your code that you want to get 2 worksheets in your workbook.
 
The post in the Forums doesn't use ODS TEXT -- it just shows how to force a new sheet after you have used sheet_interval='none', so if you try their solution and use ODS TEXT, you might have to work with Tech Support if their solution doesn't work for you.
cynthia

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 250 views
  • 0 likes
  • 2 in conversation