BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BCNAV
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Check your header in the file.

And then check the embedded_titles option.

View solution in original post

12 REPLIES 12
ballardw
Super User

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.

BCNAV
Quartz | Level 8

What command did you mean by

 

ODS TEXT EXCEL??

 

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

Reeza
Super User

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

BCNAV
Quartz | Level 8

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.

BCNAV
Quartz | Level 8

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.

Reeza
Super User

Check the bottom of the first sheet.

BCNAV
Quartz | Level 8

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

Reeza
Super User

 

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

 

BCNAV
Quartz | Level 8

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.

Reeza
Super User

Check your header in the file.

And then check the embedded_titles option.

Cynthia_sas
SAS Super FREQ

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;
BCNAV
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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