Desktop productivity for business analysts and programmers

Missing Title in SGPLOT ODS Excel

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 108
Accepted Solution

Missing Title in SGPLOT ODS Excel

[ Edited ]

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


Accepted Solutions
Solution
‎03-26-2018 05:54 PM
SAS Super FREQ
Posts: 806

Re: Missing Title in SGPLOT ODS Excel

[ Edited ]

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
;

View solution in original post


All Replies
Super User
Posts: 13,023

Re: Missing Title in SGPLOT ODS Excel


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

Frequent Contributor
Posts: 108

Re: Missing Title in SGPLOT ODS Excel

Thanks @ballardw ,

 

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

 

Cheers

 

Dean

Solution
‎03-26-2018 05:54 PM
SAS Super FREQ
Posts: 806

Re: Missing Title in SGPLOT ODS Excel

[ Edited ]

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
;
Frequent Contributor
Posts: 108

Re: Missing Title in SGPLOT ODS Excel

Posted in reply to Bruno_SAS

Thanks @Bruno_SAS 

 

 

Works now.

 

Cheers

 

Dean

Frequent Contributor
Posts: 108

Re: Missing Title in SGPLOT ODS Excel

Posted in reply to Bruno_SAS

Thanks @Bruno_SAS 

 

 

Works now.

 

Cheers

 

Dean

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 192 views
  • 0 likes
  • 3 in conversation