topic Re: ODS layout Gridded won't publish all tables in ONE PAGE! in ODS and Base Reporting

ODS layout Gridded won't publish all tables in ONE PAGE!

paris_bloom — Tue, 25 May 2021 14:50:25 GMT

I'm using ODS layout gridded and ODS PDF to publish 6 tables (2 rows and 3 columns) in one page.

However, SAS keeps separating the tables into three pages and publishes two tables on each page only.

I already stated STARTPAGE=NO in the options.

What else am I missing?

I use 6 ODS REGION statements along with the ODS GRIDDED:

Options nodate nonumber /*nocenter*/ orientation=landscape /*papersize=A4*/ leftmargin=0.25in rightmargin=0.001in bottommargin=0.001in; /*avoid date and timestamp on pdf outputs*/
ods pdf file='/test.pdf' NOGTITLE NOGFOOTNOTE STARTPAGE=no;

ods escapechar='~';

ods layout gridded
columns=2 ;

ods region ;
ods text='Text1';
proc tabulate data=sep_dataset s=[just=c ] format=comma12.2;
class servbd /order=data preloadfmt mlf;
class band2 /order=data preloadfmt mlf;
var pri;
table
servbd='Years of Service' ALL='Total'*{style=[font_weight=bold]},
band2='Age Groups'*(N='') ALL='Total'*(n='Total' colpctn='%'*f=pctfmt.)*{style=[font_weight=bold]}
/printmiss misstext='0';

where region='HQ' and status=1;
format servbd $servbd. band2 $Band2_.;
run;

ods region ;
ods text='Text2';
proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
class retfy /order=data preloadfmt mlf;
class branch;
var pri;
table
retfy='Fiscal Years' ALL='Total'*{style=[font_weight=bold]}, pri=''*(N='Number' colpctn='%'*f=pctfmt.*{style=[font_weight=bold]})/*Add column here*/
/printmiss misstext='0';
where region='HQ' and status=1;
format retfy $retfy. ;
run;

ods region ;
ods text='Text3';
proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
class group /order=data preloadfmt mlf;
class level /order=data preloadfmt mlf;
class branch;
var pri;
table
group='Group' ALL='Total'*{style=[font_weight=bold]},
level='Level'*(N='') ALL='Total'*(n='Total' colpctn='%'*f=pctfmt.)*{style=[font_weight=bold]}
/printmiss misstext='0';

where region='HQ' and status=1;
format group $group. level $level.;
run;

ods region ;
ods text='Text4';
proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
class sex/order=data preloadfmt mlf;
class branch;
var age psserv ;
table
sex='Gender' all='Total'*{style=[font_weight=bold]}, age*(mean=''*f=8.1) psserv='Years of Service'*(mean=''*f=8.1)*{style=[just=c ]}

/box='Average' printmiss misstext='0';

where region='HQ' and status=1;
format sex $sex. ;
run;

ods region;
ods text='Text5';
proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
class status /order=data preloadfmt mlf;;

class branch;
var pri ;
table branch='Program' ALL='Total'*{style=[font_weight=bold just=c]},

status='Employment Type'*(n='') ALL='Total'*{style=[font_weight=bold just=c]}*(n='Total' colpctn='%'*f=pctfmt.)
/printmiss misstext='0';

where region='HQ';
format branch $program. status status.;
run;

ods region;
ods text='Text6';
proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
class sex /order=data preloadfmt mlf;
class status;

class branch;
var pri ;
table
sex='Gender' all='Total'*{style=[font_weight=bold just=c]}, status='Employment Type'*(N='') ALL='Total'*{style=[font_weight=bold just=c]}*(N='')
/printmiss misstext='0';

where region='HQ'; *FOR HQ TABLES ONLY;
format sex $sex. status status.;
run;

footnote1 j=left 'footnote';
ods layout end;
ods pdf close;

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

paris_bloom — Tue, 25 May 2021 15:18:17 GMT

Options nodate nonumber /*nocenter*/ orientation=landscape  /*papersize=A4*/ leftmargin=0.25in rightmargin=0.001in bottommargin=0.001in; /*avoid date and timestamp on pdf outputs*/
ods pdf file='/test.pdf' NOGTITLE NOGFOOTNOTE STARTPAGE=no;


ods escapechar='~';

ods layout gridded 
columns=2  ;

ods region  ;
		ods text='Text1';
		proc tabulate data=sep_dataset s=[just=c ] format=comma12.2;
		class servbd /order=data preloadfmt mlf;
		class band2 /order=data preloadfmt mlf;
		var pri;
		table  
		servbd='Years of Service' ALL='Total'*{style=[font_weight=bold]},
		band2='Age Groups'*(N='') ALL='Total'*(n='Total' colpctn='%'*f=pctfmt.)*{style=[font_weight=bold]}
		/printmiss misstext='0'; 

		where region='HQ' and status=1; 
		format servbd $servbd. band2 $Band2_.;
		run;

ods region ;
		ods text='Text2';
		proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
		class retfy /order=data preloadfmt mlf;
		class branch;
		var pri;
		table 
		retfy='Fiscal Years' ALL='Total'*{style=[font_weight=bold]}, pri=''*(N='Number' colpctn='%'*f=pctfmt.*{style=[font_weight=bold]})/*Add column here*/
		/printmiss misstext='0'; 
		where region='HQ' and status=1; 
		format retfy $retfy. ; 
		run;

ods region ;
		ods text='Text3';
		proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
		class group /order=data preloadfmt mlf;
		class level /order=data preloadfmt mlf;
		class branch;
		var pri;
		table  
		group='Group' ALL='Total'*{style=[font_weight=bold]},
		level='Level'*(N='') ALL='Total'*(n='Total' colpctn='%'*f=pctfmt.)*{style=[font_weight=bold]} 
		/printmiss misstext='0'; 

		where region='HQ' and status=1; 
		format group $group. level $level.; 
		run;

ods region ;
		ods text='Text4';
		proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
		class sex/order=data preloadfmt mlf;
		class branch;
		var age psserv ;
		table 
		sex='Gender' all='Total'*{style=[font_weight=bold]}, age*(mean=''*f=8.1) psserv='Years of Service'*(mean=''*f=8.1)*{style=[just=c ]} 

		  /box='Average' printmiss misstext='0'; 

		where region='HQ' and status=1; 
		format sex $sex. ; 
		run;

ods region;
		ods text='Text5';
		proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
		class status /order=data preloadfmt mlf;;

		class branch;
		var pri ;
		table branch='Program' ALL='Total'*{style=[font_weight=bold just=c]}, 

		 status='Employment Type'*(n='') ALL='Total'*{style=[font_weight=bold just=c]}*(n='Total' colpctn='%'*f=pctfmt.) 
		/printmiss misstext='0'; 

		where region='HQ'; 
		format branch $program. status status.; 
		run;

ods region;
		ods text='Text6';
		proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
		class sex /order=data preloadfmt mlf;
		class status;

		class branch;
		var pri ;
		table  
		sex='Gender'  all='Total'*{style=[font_weight=bold just=c]}, status='Employment Type'*(N='') ALL='Total'*{style=[font_weight=bold just=c]}*(N='')
		/printmiss misstext='0'; 

		where region='HQ'; *FOR HQ TABLES ONLY;
		format sex  $sex. status status.; 
		run;

footnote1 j=left 'footnote';
ods layout end;
ods pdf close;

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

Reeza — Tue, 25 May 2021 15:57:04 GMT

Without your data we can't run your code.

You say you want a 2 x 3 (2 rows, 3 columns) but your statement is only asking for two columns?

ods layout gridded
columns=2 ;

What happens if you actually specify your columns/rows as well as the sizes to ensure it fits on a page?

ods layout gridded
rows = 2 columns=3 ;

EDIT: That worked for me, after removing the sizes. You can control them more explicitly if needed.

Options nodate nonumber /*nocenter*/ orientation=landscape  /*papersize=A4*/ leftmargin=0.25in rightmargin=0.001in bottommargin=0.001in; /*avoid date and timestamp on pdf outputs*/
ods pdf file='/home/fkhurshed/test.pdf' NOGTITLE NOGFOOTNOTE STARTPAGE=no;


ods escapechar='~';

ods layout gridded rows = 2 columns=3 ;

ods region  ;
		ods text='Text1';
		proc print data=sashelp.air (obs=2) noobs label;
		run;

ods region ;
		ods text='Text2';
		proc print data=sashelp.air (obs=2) noobs label;
		run;

ods region ;
		ods text='Text3';
		proc print data=sashelp.air (obs=2) noobs label;
		run;

ods region ;
		ods text='Text4';
		proc print data=sashelp.air (obs=2) noobs label;
		run;

ods region;
		ods text='Text5';
		proc print data=sashelp.air (obs=2) noobs label;
		run;

ods region;
		ods text='Text6';
		proc print data=sashelp.air (obs=2) noobs label;
		run;
		
footnote1 j=left 'footnote';
ods layout end;
ods pdf close;

@paris_bloom wrote:

Options nodate nonumber /*nocenter*/ orientation=landscape  /*papersize=A4*/ leftmargin=0.25in rightmargin=0.001in bottommargin=0.001in; /*avoid date and timestamp on pdf outputs*/
ods pdf file='/test.pdf' NOGTITLE NOGFOOTNOTE STARTPAGE=no;


ods escapechar='~';

ods layout gridded 
columns=2  ;

ods region  ;
		ods text='Text1';
		proc tabulate data=sep_dataset s=[just=c ] format=comma12.2;
		class servbd /order=data preloadfmt mlf;
		class band2 /order=data preloadfmt mlf;
		var pri;
		table  
		servbd='Years of Service' ALL='Total'*{style=[font_weight=bold]},
		band2='Age Groups'*(N='') ALL='Total'*(n='Total' colpctn='%'*f=pctfmt.)*{style=[font_weight=bold]}
		/printmiss misstext='0'; 

		where region='HQ' and status=1; 
		format servbd $servbd. band2 $Band2_.;
		run;

ods region ;
		ods text='Text2';
		proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
		class retfy /order=data preloadfmt mlf;
		class branch;
		var pri;
		table 
		retfy='Fiscal Years' ALL='Total'*{style=[font_weight=bold]}, pri=''*(N='Number' colpctn='%'*f=pctfmt.*{style=[font_weight=bold]})/*Add column here*/
		/printmiss misstext='0'; 
		where region='HQ' and status=1; 
		format retfy $retfy. ; 
		run;

ods region ;
		ods text='Text3';
		proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
		class group /order=data preloadfmt mlf;
		class level /order=data preloadfmt mlf;
		class branch;
		var pri;
		table  
		group='Group' ALL='Total'*{style=[font_weight=bold]},
		level='Level'*(N='') ALL='Total'*(n='Total' colpctn='%'*f=pctfmt.)*{style=[font_weight=bold]} 
		/printmiss misstext='0'; 

		where region='HQ' and status=1; 
		format group $group. level $level.; 
		run;

ods region ;
		ods text='Text4';
		proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
		class sex/order=data preloadfmt mlf;
		class branch;
		var age psserv ;
		table 
		sex='Gender' all='Total'*{style=[font_weight=bold]}, age*(mean=''*f=8.1) psserv='Years of Service'*(mean=''*f=8.1)*{style=[just=c ]} 

		  /box='Average' printmiss misstext='0'; 

		where region='HQ' and status=1; 
		format sex $sex. ; 
		run;

ods region;
		ods text='Text5';
		proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
		class status /order=data preloadfmt mlf;;

		class branch;
		var pri ;
		table branch='Program' ALL='Total'*{style=[font_weight=bold just=c]}, 

		 status='Employment Type'*(n='') ALL='Total'*{style=[font_weight=bold just=c]}*(n='Total' colpctn='%'*f=pctfmt.) 
		/printmiss misstext='0'; 

		where region='HQ'; 
		format branch $program. status status.; 
		run;

ods region;
		ods text='Text6';
		proc tabulate data=sep_dataset s=[just=c] format=comma12.2;
		class sex /order=data preloadfmt mlf;
		class status;

		class branch;
		var pri ;
		table  
		sex='Gender'  all='Total'*{style=[font_weight=bold just=c]}, status='Employment Type'*(N='') ALL='Total'*{style=[font_weight=bold just=c]}*(N='')
		/printmiss misstext='0'; 

		where region='HQ'; *FOR HQ TABLES ONLY;
		format sex  $sex. status status.; 
		run;

footnote1 j=left 'footnote';
ods layout end;
ods pdf close;

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

paris_bloom — Tue, 25 May 2021 18:14:48 GMT

Hi @Reeza

thank you for your response..

I tried adding the rows=3 and still no luck. I get three pages ( two tables on each page) on two columns.

My six tables are of various sizes...

How do I control them more explicitly?

I thought the point of ODS Gridded layout was to do the sizing automatically.

Your help is appreciated.

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

Reeza — Tue, 25 May 2021 18:20:48 GMT

I'm not aware of ODS GRIDDED sizing the data automatically, what makes you think it will size things automatically?

Are you tables actually sized to fit in the space you're allocating? You have adjusted the fonts and cell sizes as appropriate?

Does the example above generate a single page for you?

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

paris_bloom — Tue, 25 May 2021 18:53:45 GMT

The example you provided did in fact generate a single page for me...

but I'm not sure how to adjust my tables/fonts/cell sizes as appropriate.

Some of my tables are more than a rectangular shape, some are square (equal rows and columns), some only have two rows, some others have several rows.

But I want to be bale to fit them all in one page.

Any idea how I can size each table?

Thanks again

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

Reeza — Tue, 25 May 2021 19:04:09 GMT

Not easily AFAIK. Ideally there would be a fit to page option or something like in Excel but that doesn't exist for PDF. Layouts are a painful process in any tool IME.
https://support.sas.com/resources/papers/proceedings10/011-2010.pdf

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

paris_bloom — Tue, 25 May 2021 23:24:43 GMT

@Reeza

Unfortunately the paper did not discuss the options within the ODS GRIDDED 😞

it talked about ODS Graphics and styles and setting page size to letter/legal.

I'm still very much confused what to do with ODS Gridded. sorry I'm not very advanced with ODS reports. 😢

Can you help me a bit more?

Can you provide an example?

Thanks so much I really appreciate it!

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

ghosh — Wed, 26 May 2021 14:31:27 GMT

If your requirement is to create just one page of output, you could ODS layout ABSOLUTE.

You can then precisely define the coordinates and dimension of each region. Make sure the data will fit in those regions. You may need to use proc report instead of tabulate in order to have complete control of the line and column spacing

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

Reeza — Wed, 26 May 2021 14:59:55 GMT

I don't believe ODS GRIDDED does what you're expecting which is to scale your data down automatically. You have to manually set the font sizes (as illustrated in the paper) to force it into the space. BI tools like VA have that capability but AFAIK ODS PDF and ODS LAYOUT do not. I could be wrong so if you have a SAS license I'd check with Tech support.

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

ghosh — Wed, 26 May 2021 15:09:38 GMT

@Reeza As I mentioned in my previous post ODS LAYOUT ABSOLUTE enables precise control for PDF output. However, Proc tabulate is not ideal, IME Proc report would be better

Re: ODS layout Gridded won't publish all tables in ONE PAGE!

Reeza — Wed, 26 May 2021 15:29:21 GMT

It does enable precise control, but you have to specify those 'controls'. I think OP is looking for an option that will scale their output to fit into the specified region which is entirely different than controlling where the output goes. It's about controlling the size of the tables into the region. PROC REPORT has more options for styling directly within the PROC and TABULATE is primarily controlled via templates, so I agree that PROC REPORT is a better solution. However, I don't think Absolute will accomplish what the OP is trying to achieve. They want to put 6 tables on a single page and specify a single option such as FIT TO PAGE that will scale it to the page, similar to ODS EXCEL.

At least that's my understanding of the problem, I'll let the OP clarify what they're trying to accomplish.