topic Re: how to set number of rows on a ods excel page in ODS and Base Reporting

how to set number of rows on a ods excel page

nnl3256 — Wed, 20 Feb 2019 20:52:41 GMT

I have a table that contains more than 2400 rows of records. After ods converting to a excel file, when using print preview or printing a hard copy, the footers overlay on the last row of the page. What ods excel option can be used to set number of rows on a page to avoid overlay?

ods listing close;
	ods excel  file="C:\temp\try.xlsx" style=snow;			
	options
	(
		sheet_name = "ID and File Name" 
		absolute_column_width="10, 50"  
		autofilter="all"	 
		row_repeat="1" 
		frozen_rowheaders='yes' 
		frozen_headers='yes'
		fittopage='no'
		embedded_footnotes='yes'
		print_footer="&L&A &CPage &P of &N &R%sysfunc(today(), mmddyy10)"
	);    

	title;
	proc print data=pdflist noobs;
		var HCO_ID /style(column)=[just=right];
		varFileName /style(column)=[just=left];
	run;
	
ods excel close;
ods listing;

Re: how to set number of rows on a ods excel page

SuzanneDorinski — Fri, 22 Feb 2019 21:15:00 GMT

I used the code you provided with the dataset you provided. I didn't see the same problem in Print Preview.

In the screen shot below, the top and bottom margins in my spreadsheet look different than in your screen shot.

Footer in PRINT PREVIEW is not covering up last row of detail on the first page

Page Setup in my spreadsheet shows a top margin of 0.5 and a bottom margin of 05.

Page Setup in your spreadsheet shows a top margin of 0 and a bottom margin of 0.

I'm not sure how to fix the issue.

Re: how to set number of rows on a ods excel page

nnl3256 — Mon, 25 Feb 2019 15:23:00 GMT

Thank you for testing. I find out that the problem appears randomly by using the same code on different dataset. I may try to set orientation and margin to override the default setting. Thanks again.

Re: how to set number of rows on a ods excel page

SuzanneDorinski — Wed, 27 Feb 2019 19:36:26 GMT

Perhaps @Chevell_sas or somebody else at SAS will chime in with suggestions.

Re: how to set number of rows on a ods excel page

xxformat_com — Wed, 16 Feb 2022 09:41:11 GMT

Hi,

Here both values are expressed in inches. Centimeters can be used by bottommargin= global option but not by print_footer_margin=.

options bottommargin=1in;
      
ods excel file="&xxtest./reporting/demo.xlsx";

ods excel options(print_footer='This is a demo'
                  print_footer_margin='.5');

proc print data=sashelp.cars noobs;
run;

ods excel close;

Page Setup - On my computers values are displayed in centimeters but it depends on the language used on your computer.

1inch=2,54 centimers.

Result

Alternative syntax using a style template.

proc template;
    define style styles.xxdemo;
    parent=styles.excel;
    class body /
        marginbottom = 1in;
    end;
run;

      
ods excel file="&xxtest./reporting/demo2.xlsx" style=xxdemo;

ods excel options(print_footer='This is a demo'
                  print_footer_margin='.5');

proc print data=sashelp.cars noobs;
run;

ods excel close;