Print Line of Text to Tagsets.ExcelXp is broken into multiple lines

[Cynthia_sas]

Hi: To center the text string, you'd only need to add
just=c
to the style override on the DEFINE statement:
define message / display
       style(column)={font_size=12pt color=red font_weight=bold just=c};

This would center it inside the boundary of the PROC REPORT table. If you want the cellwidth for the message to be wider, you could add this:

define message / display
       style(column)={font_size=12pt color=red font_weight=bold just=c width=3in};

Cynthia

[SaugataM]

Yupp this works. Thanks for this. The only issue in this case is, the title cell heights (not only widths) are increased manifold in the excel file as well. Works flawlessly in the results window though. Thanks a lot for your patience with me.

data norecords;
  message = 'No Records Found';
run;

ods tagsets.excelxp file='/apps/CDRA/development/GCO_PRODUCTIVITY/Output/ECG_Patch_Listings/norecords3.xls'
    style=htmlblue options(embedded_titles='yes'
                           embedded_footnotes='yes');

title1 j=c 'Title 1 No Records';
title2 j=c 'Title 2 No Records';
footnote1 'Footnote 1 No Records';
footnote2 'Footnote 2 No Records';

proc report data=norecords noheader ls=160 ps=60;
  column message;
  define message / display 
     style(column)={font_size=10pt color=VIB cellwidth=15in just=c};
run;

ods tagsets.excelxp close;

[SaugataM]

While using this solution with ODS Excel, the title and footnote is not printing out. They are showing in the Results window but not to the output file. Also, If I use more than 1 title and footnote, the resultant file gets corrupted.

options mprint mlogic symbolgen;

%macro dummyprint(dsname);
proc sql noprint;
   select count(*) into : reccount
   from &dsname.;
quit;
%let reccount = %sysfunc(coalescec(&reccount,0));

%if &reccount=0 %then %do;
   Proc odstext;
   p "No records found";
   run;
%end;
%mend;

ods Excel file='c:\temp\CheckScenario4.xls'
options(sheet_name="Tab_Name_here" frozen_headers='5');

data class;
   stop;
   set sashelp.class;
run;

title1 "Report Name and Place here";
title2 "Report date and time here";
footnote1 j=l "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. To take a trivial example, which of us ever undertakes laborious physical exercise, except to obtain some advantage from it?"; 
footnote2 j=l "Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.";
	

%dummyprint(class);

Proc Print data=class;
Run;
   
Ods excel close;

Any idea, what is happening here?

[Tom]

Do you want ODS EXCEL to put the TITLES and FOOTNOTES into the Sheet?  Or just set them in the PRINT options on Excel?

[SaugataM]

I want Titles and Footnotes (2 of them each) to be placed in excel file along with a text message in between them, saying "No records found" in case there are 0 records in my dataset.

[Tom]

Then check out the ODS EXCEL options of EMBEDDED_TITLES and EMBEDDED_FOOTNOTES. Also the EMBED_TITLES_ONCE and EMBED_FOOTNOTES_ONCE options.

[SaugataM]

Though I have selected @Kurt_Bremser answer as the solution as it answered my initial question the best. The answer from @Cynthia_sas with Proc Report and Noheader also solves the bigger problem for me. It was a great learning for me about possibilities of ODS and excelxp tagset vs ods excel. Thanks all for your help.