topic Re: Remove empty row between title/footnote and table ODS EXCEL and PROC REPORT in ODS and Base Reporting

Remove empty row between title/footnote and table ODS EXCEL and PROC REPORT

RussellShekha — Thu, 10 Oct 2019 16:40:38 GMT

I am trying to remove the extra row between a title and table, and footnote and table when using PROC REPORT and ODS EXCEL.

When the excel file is produced there is always a row between the title and the table, and the table and footnote. I would like to remove both of these empty rows. I have provided some sample code, and a screenshot of the resulting report. The yellow rows are the ones I would like to remove. Thanks in advance!

ods excel file ="&reportpath\Daily BLAH, &MC1DS_WORDDATE..xlsx" options (

	sheet_interval = 'none'
	embedded_titles = 'on' 
	tab_color='purple' start_at='1,1'
	frozen_headers='no' 
	ROW_HEIGHTS ='14,00,0,35,0,14,0'

	
);

ods escapechar='^';

title j=c bold "Approved BLAH BLAH Requests on &MC1DS_WORDDATE.";
proc report data=sbc4 (where = (request_date=&MC1DS.)) completerows;
columns var2 approved;
define var2/ group  "Requests" style(column)={just=l}
	exclusive preloadfmt format = approved.;
define approved / analysis n "Total"  ;
run;

title j=c bold "This Count by That";
proc report data=sbc4 (where = (request_date=&MC1DS.))  ;
columns var1 var2;
define var1/ group 'Var1' ;
compute var1;
IF _break_ in ('_RBREAK_') THEN do;
	var1= 'Grand Total';
	end;
endcomp;
define var2/ analysis n 'Total';
RBREAK after / summarize ;
footnote1 "This is footnote1.";
run;

ods listing;
ods excel close;

Re: Remove empty row between title/footnote and table ODS EXCEL and PROC REPORT

data_null__ — Thu, 10 Oct 2019 18:00:24 GMT

Post and example with data that we can run.

@RussellShekha wrote:

I am trying to remove the extra row between a title and table, and footnote and table when using PROC REPORT and ODS EXCEL.

When the excel file is produced there is always a row between the title and the table, and the table and footnote. I would like to remove both of these empty rows. I have provided some sample code, and a screenshot of the resulting report. The yellow rows are the ones I would like to remove. Thanks in advance!
ods excel file ="&reportpath\Daily BLAH, &MC1DS_WORDDATE..xlsx" options (

	sheet_interval = 'none'
	embedded_titles = 'on' 
	tab_color='purple' start_at='1,1'
	frozen_headers='no' 
	ROW_HEIGHTS ='14,00,0,35,0,14,0'

	
);

ods escapechar='^';

title j=c bold "Approved BLAH BLAH Requests on &MC1DS_WORDDATE.";
proc report data=sbc4 (where = (request_date=&MC1DS.)) completerows;
columns var2 approved;
define var2/ group  "Requests" style(column)={just=l}
	exclusive preloadfmt format = approved.;
define approved / analysis n "Total"  ;
run;

title j=c bold "This Count by That";
proc report data=sbc4 (where = (request_date=&MC1DS.))  ;
columns var1 var2;
define var1/ group 'Var1' ;
compute var1;
IF _break_ in ('_RBREAK_') THEN do;
	var1= 'Grand Total';
	end;
endcomp;
define var2/ analysis n 'Total';
RBREAK after / summarize ;
footnote1 "This is footnote1.";
run;

ods listing;
ods excel close;

Re: Remove empty row between title/footnote and table ODS EXCEL and PROC REPORT

ballardw — Thu, 10 Oct 2019 21:41:21 GMT

Try this at the end of your proc report code instead of the footnote:

   compute after ;
       text='Footnote text goes here.';
       line text $50.;
   endcomp;
run;

Re: Remove empty row between title/footnote and table ODS EXCEL and PROC REPORT

Cynthia_sas — Thu, 10 Oct 2019 23:36:51 GMT

Hi:
The only thing I can think of is using hidden_rows suboption or the row_heights suboption. You can look in the documentation for examples of the full syntax. Here are examples using SASHELP.CLASS. think the row_heights suboption is probably the most flexible of the 2 methods since it doesn't depend on knowing which rows to "hide".

hidden_rows output:

row_heights output:

Hope this helps,
Cynthia

Re: Remove empty row between title/footnote and table ODS EXCEL and PROC REPORT

RussellShekha — Fri, 11 Oct 2019 16:39:21 GMT

Thank you @ballardw that did the trick with a little editing. I needed to be able to add font formatting to the lines, so this is what I went with.

To avoid an empty row between the title and the table:

compute before _page_

/ style=[font_weight=bold font_size=16pt just=center];

line "This is a title" ;

endcomp;

Between table and footnote:

compute after _page_

/ style=[font_weight=bold font_size=9pt just=center];

line "This is a footnote.";

endcomp;

Re: Remove empty row between title/footnote and table ODS EXCEL and PROC REPORT

RussellShekha — Fri, 11 Oct 2019 16:41:20 GMT

@Cynthia_sas I will give this a try as well. This might be a more direct way that allows you to keep using title and footnote in the proc. Thank you!