How to stop a title from moving to another an Excel spreadsheet in a report with three sheets?

[Hopes]

Hi everyone,

I created a report to be exported as an Excel spreadsheet. The report has three tabs with titles and contents. The title in the second sheet keeps moving to the third sheet. I tried many ways to fix that, but I was not successful. I have attached an image of the issue (First title on the top should be in the second sheet NOT in the third sheet) and the SAS code that I used to create the report. 

Thank you in advance for any help.

Amaal

[Kurt_Bremser]

Move the statements

title;
footnote;

before the ODS OPTIONS statement that starts the new sheet.

[Hopes]

Hi everyone,

I forgot to mention that when I run the code, it generates the report without any problem. However, I coded the report in SAS to be shared with internal people via email, along with a link that allows them to access the report saved in an internal folder. When I click on the link, I see the issue with the title. I think it could be an Outlook issue rather than SAS, which is another potential cause. Here is an example of the SAS code for the email: 

Data _null_;

filename outmail email

from = ("include sender emails" /*"syndsurv@health.ny.gov"*/) 

to = ("include recipients emails”)

subject="OD2A-S CuSum Signals &sysdate";

file outmail;

put "Hello All,";

put " ";

put "Yesterday &yesterday., Unintentional Overdose CuSum signals generated were as follows.";

put " ";

put "Unintentional Drug Overdose CuSum Signals";

put "There were&DRUGOD_COU signals generated at the county level.";

put "There were&DRUGOD_FAC signals generated at the hospital level.";

put " ";

put "Unintentional Opioid Overdose CuSum Signals";

put "There were&OPIOOD_COU signals generated at the county level.";

put "There were&OPIOOD_FAC signals generated at the hospital level.";

put " ";

put "Unintentional Heroin Overdose CuSum Signals";

put "There were&HEROOD_COU signals generated at the county level.";

put "There were&HEROOD_FAC signals generated at the hospital level.";

put " ";

put "Unintentional Stimulus Overdose CuSum Signals";

put "There were&STIMOD_COU signals generated at the county level.";

put "There were&STIMOD_FAC signals generated at the hospital level.";

put " ";

put "C1, C2 and C1C2 CuSum Signals";

put "There were&C1_COU C1,&C2_COU C2, and&C1C2_COU C1C2 signals generated at the county level.";

put "There were&C1_FAC C1,&C2_FAC C2, and&C1C2_FAC C1C2 signals generated at the hospital level.";

put "  ";

put "The reports are saved at &OUTPUT." ; this is output’s link that I created it to be included within the email

    put "  ";

put "Thank you,";

put "Bureau's name";

put " ";

put "--------------------------------------------------------------------";

put "office’s name";

put "office’s email";

run;

[ballardw]

---

@Hopes wrote:

Hi everyone,

I forgot to mention that when I run the code, it generates the report without any problem. However, I coded the report in SAS to be shared with internal people via email, along with a link that allows them to access the report saved in an internal folder. When I click on the link, I see the issue with the title. I think it could be an Outlook issue rather than SAS, which is another potential cause. Here is an example of the SAS code for the email: 

Since the code following the above has no Title statement you might need to tell us what part of the code shown contains your "title".

If you haven't worked with SAS very long you may not have learned that TITLE and FOOTNOTE statements persist in a given session until changed. So if a previous unrelated program has a TITLE "Some title text"; then that title will appear in all output following. You would use a simple TITLE; to clear all of the title text following. 

Best practice when posting code is to paste the text into a text or code box opened on the forum using the </> or "running man" icon that appears above the message window. Code pasted into the main message window will be formatted by the forum software and may result in artifacts that change the behavior of the code when copy/pasted into a SAS session for testing. The box also helps visually separate the text discussion of the problem from the code. Log text should go into a text box as well.

I will be a bit pedantic and say that your first code does not actually show the code you ran, only the definition of a macro. Statements prior to the macro execution  may be the cause. 

It might also help to specifically state which "title" text you do not want to appear.

The commented out code in the macro including ODS PDF might indicate as well that you are generating a PDF or some other ODS destination output as well. At which point options like GTITLE/NOGTITLE and GFOOTNOTE/NOGFOOTNOTE might come into play.

[Kathryn_SAS]

The following sample code works for me where I used SASHELP data sets. The titles and footnotes appear correctly in the Excel file and when emailed. Note the options for sending the Excel file as an attachment are required per this SAS Note: 

https://support.sas.com/kb/59/389.html 

ods _all_ close;
/* Create Reports 1*/
options pageno=1 ps=45 ls=130 orientation=landscape date center number;
/*ods pdf notoc file="&output.\&SYND._&REPORT_TYPE._&LEVEL_JURISDICTION._&sysdate..pdf";*/
ods excel file="c:\temp\test.xlsx" 
	 options(suppress_bylines="yes"
           sheet_name="Alert_Case_Listing"
           embedded_titles="yes"
		   embedded_footnotes="yes"
           embed_titles_once="yes"
		   start_at="B3");

title1 height=22pt color=Black bold f=arial "Hospital Emergency Department (ED) &syndrome_text Alert Case Listing";
footnote2 h=1.0 f=arial color=Black "Reported by NYSDOH Electronic Syndromic Surveillance System (ESSS)" ; 

proc report data=sashelp.class nowd headline headskip missing
			style (report)= [cellspacing=1 borderwidth=1 bordercolor=black font_face=Arial background=white]
            style (header) =[font_weight=bold font_size=4 just=center color=Black verticalalign=middle background=#8FBC8F]
            style(column)=[font_size=1 fontfamily=Arial verticalalign=top] ;

	compute after/ style={borderleftcolor=white borderrightcolor=white borderbottomcolor=white color=#2B382B};

	line @0 "*All syndromes are defined by patient Chief Complaints and Diagnosis Codes.";
	endcomp;

run;

Ods excel options (sheet_name="Daily_Counts");
title1 height=18pt color=Black bold f=arial "Hospital Emergency Department (ED) &syndrome_text Daily Syndrome Counts";
title2 f=arial h=1.0 "in the past 15 days at &LEVEL_JURISDICTION level" ;
footnote1 
		h=1.1 f=arial c='pink' "Pink" h=1.1 f=arial c='black' " - C1 Signal;   "  
		h=1.1 f=arial c='dark yellow' "Yellow" h=1.1 f=arial c='black' " - C2 Signal;   " 
		h=1.1 f=arial c='red' "Red" h=1.1 f=arial c='black' " - C1C2 Signal ";
footnote2 h=1.0 f=arial color=black "Reported by NYSDOH Electronic Syndromic Surveillance System (ESSS)" ; 

proc report data=sashelp.cars(obs=10) nowd headline headskip missing
			style (report)= [cellspacing=1 borderwidth=1 bordercolor=black font_face=Arial background=white]
            style (header) =[font_weight=bold font_size=4 just=center color=Black verticalalign=middle background=#8FBC8F]
            style(column)=[font_size=1 fontfamily=Arial verticalalign=top] ;
run ;

Ods excel options (sheet_name="Syndrome_Graphs"
					sheet_interval="None");
title;
footnote;
data text;
  length title $100;
  title="Short-term and Long-term Graphs for Alerted &syndrome_text Syndrome"; output;
run;

proc report data=text nowd noheader 
	style(report)=[rules=none frame=void outputwidth=50% cellspacing=0 borderwidth=0 background=white];;
  column title;
  define title / style={just=c font_weight=bold font_size=18pt verticalalign=middle color=black cellspacing=0};
 
run;

/*ODS PDF startpage=no;*/
ods graphics / reset=all height=3in width=10in;
/* short-term graph */
proc sgplot data=sashelp.class;
vbar age;
run;

footnote2 h=1.0 f=arial color=black "Reported by NYSDOH Electronic Syndromic Surveillance System (ESSS)" ; 
/* long-term graph */
proc sgplot data=sashelp.class;
vbar sex;
run;

ods excel options(sheet_interval="now");
/*ods pdf close;*/
ods _all_ close;
ods listing;

filename mymail email 
 to="first.last@yahoo.com" 
 subject="test message"
 attach=("c:\temp\test.xlsx" 
 content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" lrecl=32000); 

data _null_; 
     file mymail; 
     put "ODS Excel Test"; 
run;