Missing Title in SGPLOT ODS Excel

[DME790]

Hi All,

I know this has been discussed but I'm still having trouble getting my titles to display once exported and opened in excel.

I have added the ODS _ALL_ Close; and it exports with the title but nothing appears in the Results- SAS Report Tab.

Am I doing anything wrong here or can I change my code to show the title, and results, whether I want to look at it in SAS or in excel?

I am using SAS 9.4.

data test;
 Input Date $ Calls TRAN_CNT TranPCT;
 Datalines;

31Dec17 50272 8550  0.17 
07Jan18 116514 17122  0.15 
14Jan18 136281 19738  0.14 
21Jan18 136927 22330  0.16 
28Jan18 122398 20942  0.17 
04Feb18 155274 25898  0.17 
11Feb18 157051 24900  0.16 
18Feb18 147162 23166  0.16 
25Feb18 146725 22998  0.16 
04Mar18 150231 21788  0.15 
11Mar18 144226 19737  0.14 
18Mar18 139741 19844  0.14 
25Mar18 142968 18928  0.13 

;
Run;


ODS _ALL_ CLOSE;

ods excel file="C:/Test/misc/test.XLSX";
ODS EXCEL options(sheet_interval="none" embedded_titles='yes') gtitle gfootnote;

Proc SGPlot data=Test;

	Title "Test Data";
	VBar Date / response=TRAN_CNT  Fillattrs=(Color=BIGB);
	VLine Date / response=TranPCT y2axis lineattrs=(color=VIPK thickness=5) Legendlabel='Tran %';
	Yaxis values=(0 to 30000 by 2500);
	y2axis values=(0 to .20 by .02) Label='Tran %';
	format TRAN_CNT Comma8.;
Run;

Title;

ODS Excel Close;

Any help appreciated.

Cheers

Dean

[ballardw]

---

@DME790 wrote:

Hi All,

I know this has been discussed but I'm still having trouble getting my titles to display once exported and opened in excel.

I have added the ODS _ALL_ Close; and it exports with the title but nothing appears in the Results- SAS Report Tab.

Am I doing anything wrong here or can I change my code to show the title, and results, whether I want to look at it in SAS or in excel?

I am using SAS 9.4.

Any help appreciated.

Cheers

Dean

---

The Results window is the ODS HTML display. When you use ODS _all_ close; then you closed the HTML output and there isn't anything to send to the Results window.

WHY  are you using ODS _all_ Close? With 9.4 the default destination is ODS HTML and nothing else. IF you were sending output to other destinations such as LISTING or PDF or RTF close them after use. Otherwise your _all_ close is basically only closing the result window.

[DME790]

Thanks @ballardw ,

That makes sense. I used the ODS _ALL_ Close as I found it in another answer.

Cheers

Dean

[BrunoMueller · Accepted solution]

Using the GTITLE option says that the title will be put into the image produced by Proc SGPLOT.

If you want to have the title as text within the Excel, use the NOGTITLE, NOGFOOTNOTE option

If would also set the options as you open the Excel ODS destination like so:

ods excel file="C:/temp/test.XLSX"
  options(sheet_interval="none" embedded_titles='yes') nogtitle gfootnote
;

[DME790]

Thanks @BrunoMueller 

Works now.

Cheers

Dean

[DME790]

Thanks @BrunoMueller 

Works now.

Cheers

Dean