The SAS Output Delivery System and reporting techniques

ODS Text Only Outputting Once in Excel

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

ODS Text Only Outputting Once in Excel

I am trying to simply add a line in Excel before the output of model estimates, which are sent to excel:

 

ods excel options(start_at="1,1" sheet_name="E_&sheetname._HWA" sheet_interval="table");
		ods escapechar='~';
		ods text="~S={font_size=12pt font_weight=bold}~Holt-Winters Automatic Model";
		ods text=" ";
			proc print data=EGTASK.EST_WINTERS_AUTO_&f_var noobs; run;


ods excel options(start_at="1,1" sheet_name="E_&sheetname._HWM" sheet_interval="table");
		ods escapechar='~';
		ods text="~S={font_size=12pt font_weight=bold}~Holt-Winters Manual Model";
		ods text=" ";
			proc print data=EGTASK.EST_WINTERS_MAN_&f_var noobs; run;


 

This works well, but ONLY for the first sheet (E_&sheetname._HWA). Once I change sheets to E_&sheetname._HWM, nothing is sent to Excel. I just want:

 

Title of the model

space

Model estimates

 

Any idea why the ODS Text only works for the first sheet?

 

Cheers!


Accepted Solutions
Solution
‎08-25-2017 12:21 PM
Super User
Posts: 22,820

Re: ODS Text Only Outputting Once in Excel

Check your header in the file.

And then check the embedded_titles option.

View solution in original post


All Replies
Super User
Posts: 12,996

Re: ODS Text Only Outputting Once in Excel

ODS Text has just enough quirks it is often a last choice for me. You might try ODS TEXT EXCEL to make sure SAS knows which destination is intended.

 

The amount of text and minimal styles applied may make a TITLE statement more appropriate.

Frequent Contributor
Posts: 84

Re: ODS Text Only Outputting Once in Excel

What command did you mean by

 

ODS TEXT EXCEL??

 

I had tried title, but it never appear in Excel, only the SAS output

Super User
Posts: 22,820

Re: ODS Text Only Outputting Once in Excel

Try using PROC ODSTEXT instead, it's relatively new.

Frequent Contributor
Posts: 84

Re: ODS Text Only Outputting Once in Excel

[ Edited ]

Sadly that doesn't work either.

 

Very strange.  If I use title before proc print, it will appear in the SAS output, but now where in Excel.

 

Even if I do something simple like:

 

ods excel options(start_at="1,1" sheet_name="E_&sheetname._HWA" sheet_interval="table");
	ods text="Test1";
	ods text=" ";
	proc print data=EGTASK.EST_WINTERS_AUTO_&f_var noobs; run;

ods excel options(start_at="1,1" sheet_name="E_&sheetname._HWMAN" sheet_interval="table");
	ods text="Test2";
	ods text=" ";
	proc print data=EGTASK.EST_WINTERS_MAN_&f_var noobs; run;

The text will only appear in the first sheet, no others. Is there another way to send something to Excel to ID the model estimate output? The sheet names could be used, but I'm hoping there is a way to add a nicer title to the Excel. 

 

Seems like SAS will only output to first worksheet for some reason.

Frequent Contributor
Posts: 84

Re: ODS Text Only Outputting Once in Excel

Actually it is the pagination that is off.

 

So if I use my original code:

 

ods excel options(start_at="1,1" sheet_name="E_&sheetname._HWA" sheet_interval="table");
	ods escapechar='~';
	ods text="~S={font_size=12pt font_weight=bold}~Holt-Winters Automatic Model";
	ods text=" ";
	proc print data=EGTASK.EST_WINTERS_AUTO_&f_var noobs; run;

ods excel options(start_at="1,1" sheet_name="E_&sheetname._HWM" sheet_interval="table");
	ods escapechar='~';
	ods text="~S={font_size=12pt font_weight=bold}~Holt-Winters Manual Model";
	ods text=" ";
	proc print data=EGTASK.EST_WINTERS_MAN_&f_var noobs; run;

it will print out the first title nicely...which is great.

 

 

The second one appears, but on the wrong sheet. for the ODS text, SAS is ignoring the fact that I have set a new sheet name when I say:

 

ods excel options(start_at="1,1" sheet_name="E_&sheetname._HWM" sheet_interval="table");

 

It puts the text I want into another worksheet based on the pagination of the SAS output itself. Is there a work around for this ? I am not sure. A user above suggested ODS TEXT EXCEL, but I am not sure what this is.

Super User
Posts: 22,820

Re: ODS Text Only Outputting Once in Excel

Check the bottom of the first sheet.

Frequent Contributor
Posts: 84

Re: ODS Text Only Outputting Once in Excel

@Reeza

 

It is at the bottom of the third. The program outputs 3 parts of each model to three worksheets in excel.  The 3 parts are:

 

1. Model Esitmates.

2. Forecast series

3. Graph of history and forecast

 

So the first title appears perfectly on the estimates page, but the title for the next model, which should appear on the 4th sheet (the start of a new model output), appears on the bottom of the 3rd.  SAS ignores the new worksheet declaration when using ODS Text is seems.

Super User
Posts: 22,820

Re: ODS Text Only Outputting Once in Excel

 

Title and Footnote statements do work, if that's an option at all. 

 

There may be a solution in the comments here

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets...

 

Frequent Contributor
Posts: 84

Re: ODS Text Only Outputting Once in Excel

I've seen that blog...thanks though.

 

Sadly title doesn't work...the Title will not be exported to Excel at all:

 

ods excel options(start_at="1,1" sheet_name="E_&sheetname._HWA" sheet_interval="table");
	title "Holt-Winters Automatic Model";
	proc print data=EGTASK.EST_WINTERS_AUTO_&f_var noobs; run;

ods excel options(start_at="1,1" sheet_name="E_&sheetname._HWM" sheet_interval="table");
	title "Holt-Winters Manual Model";
	proc print data=EGTASK.EST_WINTERS_MAN_&f_var noobs; run;

 

Unless the title is in the wrong spot. SAS output has title fine, but it doesn't appear in the Excel file.  Maybe this can't be done.

Solution
‎08-25-2017 12:21 PM
Super User
Posts: 22,820

Re: ODS Text Only Outputting Once in Excel

Check your header in the file.

And then check the embedded_titles option.

SAS Super FREQ
Posts: 9,253

Re: ODS Text Only Outputting Once in Excel

Hi:

  Here's some code to get you started as a test. With the embedded_titles="yes" suboption, I DO see the titles in every sheet. You should be able to test this code as it uses SASHELP files.

 

cynthia

 

ds excel file='c:\temp\showtitles.xlsx'
    options(sheet_name="one" embedded_titles='yes');

	title 'Report1, Title1';
	title2 'Report2, Title2';
	proc print data=sashelp.class;
	run;
 
ods excel options(sheet_name="two" embedded_titles='yes');

   title 'Report2, Title1';
   title2 'Report2, Title2';
 
   proc freq data=sashelp.cars;
     tables type / nocum nopercent;
   run;
 
ods excel options(sheet_name="three" embedded_titles='yes');
 
   title 'Report3, Title1';
   title2 'Report3, Title2';
   proc means data=sashelp.class min mean median max css;
     var height;
	 class age;
   run;
 
ods excel close;
Frequent Contributor
Posts: 84

Re: ODS Text Only Outputting Once in Excel

Posted in reply to Cynthia_sas

@Reeza & @Cynthia_sas

 

You both are right with embedded titles. This is the only way to get them to work properly. ODS TEXT seems to be buggy in this sense. Thanks to you both!

☑ This topic is solved.

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

Discussion stats
  • 12 replies
  • 779 views
  • 0 likes
  • 4 in conversation