ODS pdf fit table to one page

[jacupp711]

I am creating a PDF table with 12 columns that I want to all fit on one page. I've read other posts and adjusted my code to what I have below:

options
nonumber
orientation = landscape
papersize=letter
NODATE
leftmargin=.04in
rightmargin=.04in
topmargin=0in
bottommargin=0in
label
nobyline
missing=''
;

ODS PDF FILE= 'jac.pdf' NOGTITLE NOGFOOTNOTE NOTOC STARTPAGE=never;

ODS escapechar='^';

title;

ods pdf text= "^S={ font=(Arial, 12pt, bold) color=CX003366 just = left} NOV HTP Performance Report - &fk_StartDate.";
ods pdf text='^S={ font=(Arial, 8pt, bold) just=left font_weight=bold color = CXC01933} ____________________________________________________________________________________________________________________________________________________________________________________';

ods pdf text= "^S={ font=(Arial, 8pt, bold) color=CX003366 just = center} ";
ods pdf text= "^S={ font=(Arial, 10pt, bold) color=CX003366 just = center} Total FK (excl. Orig) count in LOF";

/*Proc report output - Table 1*/
PROC REPORT DATA = _001_raw_mstr1_cmbd1_SAS1_1 nowindows split='~' spanrows
style(header) = [background=CX003366 foreground=white font=('Arial', 8pt) font_weight=bold]
style(column) = [font = ('Arial', 8pt)]
style (report) = {width = 100% cellspacing=.15pt cellpadding=2pt}
;
COLUMN FK_type KPI ref_point FK_Type_in_LOF_0 FK_Type_in_LOF_1 FK_Type_in_LOF_2 FK_Type_in_LOF_3 FK_Type_in_LOF_4 FK_Type_in_LOF_5 FK_Type_in_LOF_6 FK_Type_in_LOF_7 FK_Type_in_LOF_8;
DEFINE FK_type/'FK Type' display center group order=data style={cellheight = .5cm cellwidth = 2.3cm} style(column)=[vjust=middle just=left];
DEFINE KPI/'KPI' display center group order=data style={cellheight = .5cm cellwidth = 2.3cm} style(column)=[vjust=middle just=left];
DEFINE ref_point/'Ref Point' display center group order=data style={cellheight = .5cm cellwidth = 2.3cm} style(column)=[vjust=middle just=left];
DEFINE FK_Type_in_LOF_0 /'0 Thru FK in LOF' display center f=numeric9.2 style(column)=[width=10%] style={cellheight = .5cm cellwidth = 2.3cm};
DEFINE FK_Type_in_LOF_1/'1 Thru FK in LOF' display center f=numeric9.2 style(column)=[width=10%] style={cellheight = .5 cm cellwidth = 2.3cm};
DEFINE FK_Type_in_LOF_2/'2 Thru FK in LOF' display center f=numeric9.2 style(column)=[width=10%] style={cellheight = .5 cm cellwidth = 2.3cm};

The table is going on landscape letter paper with .4 in margins. So I made each column 2.3cm which should make them fit all on one page. This adjusted the widths but the last 4 columns were still pushed to page 2. Then I added the width=100% line this just added white space between each column and still pushed the last 4 columns on another page. Any other suggestions? Pictures of the columns pushed together and spread apart are attached.

[Cynthia_sas]

Hi:

  For some tips about dealing with wide files, I suggest this paper: https://support.sas.com/resources/papers/proceedings14/SAS038-2014.pdf

  Also, I suggest cleaning up your WIDTH= and CELLWIDTH= specifications. You are providing conflicting instructions. WIDTH and CELLWIDTH specify the SAME style attribute and your STYLE= override is incorrect. You don't have an area for the second override shown below:

Rather than try to "over-control" PDF, I prefer to let PDF do the bulk of the hard work. For example, SASHELP.CARS has 15 columns and this worked quite nicely for me:

I think the thing to do is to start by simplifying your program, see whether width=100% and no other width specifications work for you and then gradually add in style overrides and be sure to use the SAME unit of measure for all the width overrides. For example, if I were going to control at the individual column level, then I would abandon the % specification entirely and only specify CM or IN -- also, since you've specified your margins in INCHES as the unit of measure. It doesn't make sense to specify CM for the individual columns. You are forcing PDF to do multiple conversions.

Hope this helps,

Cynthia

[jacupp711]

Thank you!! I was definitely over complicating it and this worked. The only issue is that now some columns (ones with text) are very wide and others (with a single number for example) are very slim. Can I add a formatting line that will distribute the width more evenly or can I add something to each line that will force a width without it causing issues like it did before? I'll play with it myself but would appreciate your direction.

Thank you!

[Cynthia_sas]

Hi:

  This is where I would get rid of the width=100% for style(report) and move to column level overrides in the DEFINE statement, as shown below:

  Notice that PDF does a nice job by itself in between the margin areas with only a few width changes and header changes so that centering looks good now. PDF tries to fit all the columns on one report row. It is only when you try to overcontrol the widths or specify conflicting widths that you get the odd spaces between columns.

  I would also make use of the split character to "tweak" the column headers so that a long word like Cylinders or Horsepower is taking up less header room and will fit in a smaller column size.

Cynthia