Od Excel Title Formatting

[ark123]

I am exporting multiple tables to excel that I would like to place a title above. The code I have tried produces two rows above my table - the top row includes my title across all cells and the row below that is blank. I only want one row and for it to only be across cells A-H, if possible.

options nobyline;
ods noproctitle;
ods excel file = "Filepath\table.xlsx";
ods Excel options(sheet_interval="bygroup" sheet_name="#byval(facility)" embedded_titles='yes' flow = "header, data" absolute_column_width = "&length_var" absolute_row_height = "20px");
title "Report" ;
proc report data = table ; 
where OOHB = 0 and result ne ('OK') and Current_Location in (" ", "Outpatient"); 
by facility;
define patientid / 'PatientID'; 
define Patient_Last_Name / 'Last Name' ; 
define Patient_First_Name / 'First Name' ;
define Patient_Date_of_Birth / 'Patient DOB' ;
define P_location / 'Location';
define Quality / 'Errors';
run;
ods excel close;

This is the excel output:

[Ksharp]

ods excel file = "c:\temp\table.xlsx" options(sheet_interval='none' Hidden_Rows='2-4');
title;
proc report data = sashelp.heart(obs=1) nowd ; 
columns status sex bp_status weight height Diastolic Systolic Smoking;
compute before _page_/style={fontsize=10 fontweight=bold vjust=m};
line 'xxxxxxxxx';
endcomp;
run;
proc report data = sashelp.heart(obs=10) nowd ; 
run;
ods excel close;

[ark123]

Your suggestion did not work for my data and goals. My dataset contains multiple facilities. I run the program so that data for each individual facility is exported as its own tab in excel. For example, if there are 10 facilities in my dataset then the excel file will have 10 tabs. 

I adjusted my code following your example (see below). It did hide the second blank row, but now I have 3 blank hidden rows between the title row and the data table. Also, now the top row with the title is across all columns and the title is 'The SAS System'. I provide this to someone how then copy and pastes it, so having those blank rows require the entire table to be selected, rather than just columns A-G.

I would like to only have one row above my table with the title: Report (across columns A-G).

data test8;
	set test7;
	where OOHB = 0 and result ne ('a_OK') and Current_Location in (" ", "Outpatient"); 
run;

ods excel file = "Filepath\table.xlsx";
ods Excel options (sheet_interval='none' hidden_rows='2-4');  
proc report data = test8 (obs=1) nowd; 
	columns patientid MRN Patient_Last_Name Patient_First_Name Patient_Date_of_Birth p_location Quality;
	compute before_page_/style={fontsize=10 fontweight=bold vjust=m};
	line 'Report';
	endcomp;
	run;
ods excel options (sheet_interval="bygroup" sheet_name="#byval(facility)" embedded_titles='yes' flow = "header, data" absolute_column_width = "&length_var" absolute_row_height = "20px");
proc report data = test8 (obs=10) nowd;
by facility;
define patientid / 'PatientID'; 
define Patient_Last_Name / 'Last Name' ; 
define Patient_First_Name / 'First Name' ;
define Patient_Date_of_Birth / 'Patient DOB' ;
define P_location / 'Location';
define Quality / 'Errors';
run;
ods excel close;

Ideally, I would like the output to look like this:

[Tom · Accepted solution]

Seems simple enough.

filename xlsx "%sysfunc(pathname(work))/example.xlsx";
ods excel file=xlsx
   options (sheet_interval="bygroup" sheet_name="Gender_#byval(sex)")
;
proc sort data=sashelp.class out=class;
  by sex name;
run;

options nobyline;
proc report data=class ;
  by sex;
  columns ('Report' name sex age) height weight;
run;
options byline;
ods excel close;

If you really want each of those other cells above the rest of the fields as separate cells instead of merged then work a little harder in construction the COLUMNS statement.

  columns ('Report' name sex age) (' ' height) ('a0'x weight);

[ark123]

That provided me with the output I was looking for. Thank you!