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!
Check your header in the file.
And then check the embedded_titles option.
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.
What command did you mean by
ODS TEXT EXCEL??
I had tried title, but it never appear in Excel, only the SAS output
Try using PROC ODSTEXT instead, it's relatively new.
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.
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.
Check the bottom of the first sheet.
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.
Title and Footnote statements do work, if that's an option at all.
There may be a solution in the comments here
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.
Check your header in the file.
And then check the embedded_titles option.
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;
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: