The SAS Output Delivery System and reporting techniques

Merging Cells Vertically in PROC REPORT PDF

Reply
New Contributor
Posts: 3

Merging Cells Vertically in PROC REPORT PDF

I have a rather unique question regarding PROC REPORT and ODS PDF. I have currently assembled a schedule-like table using PROC REPORT and in order to make this table fit on one page, I have to reduce the font size down to 3pt. While everything at this point looks alright, it also forces the text in my groups to be at 3pt as well. Since the text in the group column needs to be readable, I want to be able to increase the font size. Just adding a style line to increase the font stretches the row which distorts the scale of the schedule, so merging the cell with the text with the cell below (the report is using SPANROWS and there is plenty of space between the groups) so I can utilize that blank space without stretching out the table.

ASCII Visual

Current:

Hour | Monday | ...

---------|-------------|

7:00   |     x      |

---------|-------------|

         |      x     |

---------|-------------|

Result:

Hour | Monday | ...

---------|-------------|

7:00   |     x      |

         |-------------|

         |      x     |

---------|-------------|

(Assume the "7:00" can stretch further into the space between the two cells)

If anyone can figure this out, or point me to a reasonable alternative (I am using ODS REGION to put this next to another table) I would greatly appreciate it.

Super User
Posts: 17,784

Re: Merging Cells Vertically in PROC REPORT PDF

Try looking at the spanrows options in proc report:

http://support.sas.com/resources/papers/proceedings11/246-2011.pdf

ods pdf file='c:\spanrows.pdf';

proc report nowd data= spanrows_example spanrows;

col sex age name height weight;

define sex / order;

run;

ods pdf close;

New Contributor
Posts: 3

Re: Merging Cells Vertically in PROC REPORT PDF

I'm already using spanrows and used the PDF you linked to heavily for the rest of the report. While spanrows gives me the appropriate look, all it does is hide the extra borderlines and instances of duplicate group values. The cell issue as I described in my original diagram still persists even though the visuals imply otherwise.

SAS Super FREQ
Posts: 8,742

Re: Merging Cells Vertically in PROC REPORT PDF

Hi:

  Spanrows and PDF has a known issue where the first row of a group gets extra space added to it, as described in this Tech Support note:

http://support.sas.com/kb/45/549.html

  It's hard for me to envision what you mean by "distorts the scale", since your drawing seems to me to show what you want, not what you're getting. And, since you didn't post your code, it's hard to tell whether the fix described in the Tech Support note is something you already tried.

  If trying both cellwidth and cellheight do not work for you, as described in the Tech Support note (they have code that illustrates both the problem and the fix), then I'd recommend opening a track with Tech Support. They can look at all your code and help you figure out whether you have discovered a new problem or figure out if there's a workaround.

cynthia

New Contributor
Posts: 3

Re: Merging Cells Vertically in PROC REPORT PDF

In theory that support should work, but I'm not sure if a larger font falls into the same guidelines of long text.

The following image shows the distortion. The horizontal gridlines are present to make it easier to see. The line with the '10' is in 6pt font which stretches the first row of the hour while the remaining rows stay in 3pt font. If I bring the '10' back to 3pt it will all look proper.

SASIssue.png

As for the code, I am only including a part of the code as the rest is just toying with borders and quite lengthy:

-------------------------

proc report data=work.bartrans spanrows contents="&facil" nowd

        style(report)=[/*rules=none frame=void*/ cellspacing=0 cellpadding=0 fontsize=3pt]

        style(column)=[textalign=c];

        column building hour obs _emptym monday _emptyt tuesday _emptyw wednesday _emptyr thursday _emptyf friday _emptys;

       

        define hour/  order style(column) = {borderleftcolor=black borderleftwidth=3

            bordertopwidth=0 borderbottomwidth=0 fontsize=6pt};

        compute hour;

            call define(_COL_, 'style', 'style=[bordertopwidth=0 borderbottomwidth=0 borderleftwidth=1 borderrightwidth=1]');

        endcomp;

        define building / noprint order;

        define obs / noprint order;

       

        define monday / 'Mon' style(column) = {background=$ticklight. foreground=$ticklight.

            borderleftcolor=black borderleftwidth=2 borderrightwidth=2 fontsize=1pt cellwidth=50};

       

        define tuesday / 'Tue' width=30 style(column) = {background=$ticklight. foreground=$ticklight.

            borderleftcolor=black borderleftwidth=2 borderrightwidth=2 fontsize=1pt cellwidth=50};

        define wednesday / 'Wed' width=30 style(column) = {background=$ticklight. foreground=$ticklight.

            borderleftcolor=black borderleftwidth=2 borderrightwidth=2 fontsize=1pt cellwidth=50};

        define thursday / 'Thr' width=30 style(column) = {background=$ticklight. foreground=$ticklight.

            borderleftcolor=black borderleftwidth=2 borderrightwidth=2 fontsize=1pt cellwidth=50};

        define friday / 'Fri' width=30 style(column) = {background=$ticklight. foreground=$ticklight.

            borderleftcolor=black borderleftwidth=2 borderrightwidth=2 fontsize=1pt cellwidth=50};

        compute obs;

            if obs < 5 or obs > 180 then do;

                call define(_ROW_, 'style', 'style=[borderleftwidth=0 borderrightwidth=0]');

            end;

            else if obs = 5 or obs = 41 or obs = 77 or obs = 113 or obs = 131 then do;

                call define(_ROW_, 'style', 'style=[borderbottomcolor = black borderbottomwidth=2 borderleftwidth=0 borderrightwidth=0]');

            end;

            else if obs = 40 or obs = 76 or obs = 112  or obs = 130 or obs = 180 then do;

                call define(_ROW_, 'style', 'style=[bordertopcolor = black bordertopwidth=2 borderleftwidth=0 borderrightwidth=0]');

            end;

        endcomp;

        define _emptym/' ' style(column) = {cellwidth=1%    borderleftcolor=black bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 fontsize=3pt};

        define _emptyt/' ' style(column) = {cellwidth=1%    borderleftcolor=black bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 fontsize=3pt};

        define _emptyw/' ' style(column) = {cellwidth=1%    borderleftcolor=black bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 fontsize=3pt};

        define _emptyr/' ' style(column) = {cellwidth=1%    borderleftcolor=black bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 fontsize=3pt};

        define _emptyf/' ' style(column) = {cellwidth=1%    borderleftcolor=black bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 fontsize=3pt};

        define _emptys/' ' style(column) = {cellwidth=1%    borderleftcolor=black bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 fontsize=3pt};

.

.

.

run;

---------------------------------------

SAS Super FREQ
Posts: 8,742

Re: Merging Cells Vertically in PROC REPORT PDF

Hi:

I think that you might be trying to overcontrol the border lines. If you use SPANROWS, there are not any divider lines in the row header that spans the rows for the group, so there's no real need to turn anything off in that column. For example, in the example, below, I make all the fonts 3pt, except for the font for PRODUCT, which is  6pt. I do not see the distortion that you experience with SPANROWS and the code described in the Tech Support note.

  I think your best bet for help is to work with Tech Support. They can look at all of your data, and at your entire program and help you figure out what the issue is.

cynthia

options orientation=portrait topmargin=.5in bottommargin=.5in

        rightmargin=.5in leftmargin=.5in;

       

ods listing close;

ods pdf file='c:\temp\testfont.pdf' notoc;

     

proc report data=sashelp.shoes nowd spanrows

     style(header)={font_size=3pt}

     style(column)={font_size=3pt};

  title '2) specify spanrows with cellwidth and cellheight';

  where region = 'Canada' and product in ('Boot', 'Sandal', 'Slipper');

  column region product sales inventory returns stores;

  define region / order noprint;

  define product / order

         style(header)={font_size=6pt}

         style(column)={font_size=6pt cellwidth=.5in cellheight=.2in};

  define sales / 'Mon';

  define inventory / 'Tues';

  define returns / 'Wed';

  define stores / 'Thurs';

  define subsidiary / 'Fri';

run;

    

ods pdf close;

Ask a Question
Discussion stats
  • 5 replies
  • 3200 views
  • 1 like
  • 3 in conversation