topic Re: ODS PDF, Proc Report - Spanrows and Row height in ODS and Base Reporting

ODS PDF, Proc Report - Spanrows and Row height

MarcusMaher — Tue, 23 Apr 2013 20:50:10 GMT

Hi,

I am trying to do vertical merging in proc report in ods pdf. If the text in the merged cell needs to be wrapped, by default SAS is making the first ropdf w in the group high enough to hold the entire text (which is undesirable). See first example:

proc template;
define style test;
parent=styles.sansprinter;
class vcenter from headersandfooters /
vjust=center
cellwidth=1.25in;
end;
quit;

ods pdf file = "repeat.pdf" style=test;
proc report data=sashelp.shoes nowd spanrows;
column Region Product Subsidiary Stores Sales Inventory Returns;
define Region / group style=vcenter;
define Product / group style=vcenter;
define Subsidiary / group;
define Stores / group;
define Sales / group;
define Inventory / group;
define Returns / group;
run;
ods pdf close;

If, as part of a style I use cellheight= then the vjust=center option doesn't seem to work all the time. Run the code below and notice the problem with Asia/Women's casual.

proc template;

define style test;

parent=styles.sansprinter;

class vcenter from headersandfooters /

vjust=center

cellwidth=1.25in

cellheight=.15in;

end;

quit;

ods pdf file = "repeat.pdf" style=test;

proc report data=sashelp.shoes nowd spanrows;

column Region Product Subsidiary Stores Sales Inventory Returns;

define Region / group style=vcenter;

define Product / group style=vcenter;

define Subsidiary / group;

define Stores / group;

define Sales / group;

define Inventory / group;

define Returns / group;

run;

ods pdf close;

Thanks!

Marcus

Re: ODS PDF, Proc Report - Spanrows and Row height

Cynthia_sas — Wed, 24 Apr 2013 00:53:01 GMT

Hi:

There was a Tech Support note about this 45549 - Wrapped text strings in the first column cause unwanted white space in subsequent rows in PROC REPORT and PROC TABULATE output routed to ODS PDF

I think this is the same issue that you are running into. There is a simple fix as described in the note.

cynthia

Re: ODS PDF, Proc Report - Spanrows and Row height

MarcusMaher — Wed, 24 Apr 2013 01:14:44 GMT

Hi Cynthia,

I am aware of that attempted fix, but in my particular example it does not work as desired (the second example shows how it fails). Look at Asia/Women's casual. Women's casual is no longer centered vertically properly.

define style test;

parent=styles.sansprinter;

class vcenter from headersandfooters /

vjust=center

cellwidth=1.25in

cellheight=.15in;

end;

quit;

ods pdf file = "repeat.pdf" style=test;

proc report data=sashelp.shoes nowd spanrows;

column Region Product Subsidiary Stores Sales Inventory Returns;

define Region / group style=vcenter;

define Product / group style=vcenter;

define Subsidiary / group;

define Stores / group;

define Sales / group;

define Inventory / group;

define Returns / group;

run;

ods pdf close;

Re: ODS PDF, Proc Report - Spanrows and Row height

Cynthia_sas — Wed, 24 Apr 2013 03:48:37 GMT

Hi:

First, you have the incorrect value for the VJUST attribute -- it is TOP, MIDDLE or BOTTOM (T, M, B) so CENTER is not a valid value (SAS(R) 9.2 Output Delivery System: User's Guide). Next, if the fix in the Tech Support note doesn't work for you, then I would suggest you open a track with Tech Support. Third, I would see whether the change works as a simple style= override first before moving into style template world.

For example, when I run your code using SAS 9.3 and ODS PDF, without a style template and using only the techniques mentioned in the TS note, I don't see any issue.

cynthia

ods pdf file = "c:\temp\vjust_mid.pdf" notoc;

proc report data=sashelp.shoes nowd spanrows;

where region in ('Asia', 'United States') and

(product contains 'Casual' or product contains 'Dress');

column Region Product Subsidiary Stores Sales Inventory Returns;

define Region / group

style(header)={vjust=middle}

style(column)={vjust=middle cellheight=.2in};

define Product / group

style(header)={vjust=middle}

style(column)={vjust=middle cellheight=.2in};

define Subsidiary / group

style(header)={vjust=middle}

style(column)={vjust=middle cellheight=.2in};

define Stores / group;

define Sales / group;

define Inventory / group;

define Returns / group;

run;

ods pdf close;

Re: ODS PDF, Proc Report - Spanrows and Row height

MarcusMaher — Wed, 24 Apr 2013 14:39:37 GMT

Thanks Cynthia,

This example does work. It's unfortunate that I can't use a style template but at least I should be able to get it to work.

Re: ODS PDF, Proc Report - Spanrows and Row height

MarcusMaher — Wed, 24 Apr 2013 16:07:51 GMT

Hi Cynthia,

Upon further review it actually didn't work - I shrank the width of the column down to force wrapping and I still have the same issue. I will submit the problem to technical support.

ods pdf file = "repeat.pdf" style=test;

proc report data=sashelp.shoes nowd spanrows;

column Region Product Subsidiary Stores Sales Inventory Returns;

define Region / group ;

define Product / group ;

define Subsidiary / group;

define Stores / group;

define Sales / group;

define Inventory / group;

define Returns / group;

compute returns;

call define(1,'style','style={vjust=middle cellwidth=.75in cellheight=.15in}');

call define(2,'style','style={vjust=middle cellwidth=.75in cellheight=.15in}');

endcomp;

run;

ods pdf close;

Re: ODS PDF, Proc Report - Spanrows and Row height

MarcusMaher — Mon, 29 Apr 2013 19:44:09 GMT

For posterity, from technical support:

You can't control for height AND width- but this should work to prevent the wrapped text. It is considered a workaround to an outstanding issue.

Re: ODS PDF, Proc Report - Spanrows and Row height

bullman — Thu, 26 Dec 2013 15:49:04 GMT

Has this issue been addressed? I have a similar issue -- row height is massive due to accounting for the width in the height.

Is there a useable solution, or am i better off writing a vba macro to shrink the height of the first x rows?

Re: ODS PDF, Proc Report - Spanrows and Row height

Cynthia_sas — Thu, 26 Dec 2013 17:17:23 GMT

Hi:

When running a version of the original poster's code in SAS 9.4, I see that you still CANNOT specify both width and height (see #1 report). However, if you only specify cellwidth, then the height autoadjusts in PDF. See the screen shots from running the attached code. Since you did not post any code, it is impossible to comment on what you should do or to even understand what issue you are encountering. I do not find row height to be "massive" when I code cellwidth only (see #2 report). And, when I have 1 observation with a really long value (see #3 row for Alfred) and another observation with a value that is not long (see #3 row for Alice), I do not find the row height to be "massive" for -both- of the rows. The row height is appropriate for all the rows in the table when I control for cellwidth only.

If you are having specific issues, then I would suggest you open a track with Tech Support, where they can look at ALL of your code and give you specific recommendations based on real code, with real results. Saying your row height is "massive" without providing data or code or results doesn't give people enough information to make constructive comments. Any comments on what you should do to "fix" the problem (in my opinion), will be fairly meaningless, since there is not enough information to understand what the problem really is.

Cynthia

proc sort data=sashelp.shoes out=shoes;
by region product subsidiary;
where region in ('Asia', 'Pacific') and
product in ('Boot', 'Slipper', "Women's Dress", "Women's Casual");
run;

ods _all_ close;

options leftmargin=.25in rightmargin=.25in topmargin=.25in bottommargin=.25in orientation=portrait;

ods pdf file = "c:\temp\cellwidth_question_1_2.pdf" style=printer;

proc report data=shoes nowd spanrows;
title "1) specify both width and height -- see Asia for Women's Dress";
column Region Product Subsidiary Stores Sales Inventory Returns;
define Region / group style(header)={background=pink}
style(column)={background=pink vjust=middle cellwidth=.75in cellheight=.15in};
define Product / group style(header)={background=yellow}
style(column)={background=yellow vjust=middle cellwidth=.75in cellheight=.15in};
define Subsidiary / group;
define Stores / group;
define Sales / group;
define Inventory / group;
define Returns / group;
run;

proc report data=shoes nowd spanrows;
title "2) specify width only -- see Asia for Women's Dress";
column Region Product Subsidiary Stores Sales Inventory Returns;
define Region / group style(header)={background=pink}
style(column)={background=pink vjust=middle cellwidth=.75in};
define Product / group style(header)={background=yellow}
style(column)={background=yellow vjust=middle cellwidth=.75in};
define Subsidiary / group style(column)={vjust=m};
define Stores / group style(column)={vjust=m};
define Sales / group style(column)={vjust=m};
define Inventory / group style(column)={vjust=m};
define Returns / group style(column)={vjust=m};
run;

ods pdf close;

data longtxt;
length longvar $270
         shortvar $12 shortvar2 $13 shortvar_nl $25;
set sashelp.class(obs=2);
shortvar = 'aaaaaaaaaaaa';
shortvar_nl = 'aaaaaa~{newline 1}aaaaaa';
if _n_ = 1 then do;
longvar = catx(' ','Twas brillig and the slithy toves.',
                 'Did gyre and gimble in the wabe',
                 'All mimsy were the borogroves',
                 'And the mome raths outgrabe.',
                 'Beware the Jabberwock my son',
                 'The jaws that bite, the claws that snatch.',
                 'Beware the jubjub bird and shun',
                 'the frumious Bandersnatch.');
end;
else if _n_ = 2 then longvar = 'xxxxx';
longvar2 = longvar;
run;

ods _all_ close;

options leftmargin=.25in rightmargin=.25in topmargin=.25in bottommargin=.25in orientation=landscape;

ods pdf file='c:\temp\cellwidth_question_3.pdf' style=printer;
ods escapechar='~';

proc report data=longtxt nowd;
    column name shortvar shortvar_nl longvar longvar2;
    title '3) Really LONG char var wraps based on cellwidth';
    define name / display style(column)={just=c};
    define shortvar/ display
           style(column)={cellwidth=.10in};
    define shortvar_nl/display;
    define longvar/ display
           style(column)={cellwidth=2in};
    define longvar2/display
           style(column)={cellwidth=4in};
run;
ods _all_ close;
title; footnote;

Re: ODS PDF, Proc Report - Spanrows and Row height

bullman — Thu, 02 Jan 2014 21:33:58 GMT

Cynthia,

Thank you for the in depth response. I was overly vague in my intial question and should have specified that I am outputting to excel via excel XP. As far as I can tell, the title row height does not adjust for how many columns I will have, and thus ends up as wide as would be necessary if the text were wrapped in a standard cell's width.

Here is an snippet of my code:

ods select all;

ods tagsets.excelxp style=styles.slsrep10

OPTIONS ( Sheet_Name = "tabname"

Sheet_Interval = 'proc'

Orientation = 'landscape'

FitToPage = 'yes'

Pages_FitWidth = '1'

Center_Horizontal = 'yes'

Embedded_Titles = 'yes'

Embedded_Footnotes = 'yes'

Autofilter = 'yes'

Frozen_Headers = '7'

Row_Repeat = '1-7'

Absolute_Column_Width = '7,12,12,12,12,12,12,12' );

proc report data=datasetname nowd;

TITLE1 font=swiss color=black bcolor=CXFFFFFF "Title XYZ"

;

Title2 font=swiss color=black bcolor=CXFFFFFF "Really REALLY REALLY REALLY long title"

Title3 font=swiss color=black bcolor=CXFFFFFF "Really REALLY REALLY REALLY REALLY long title"

Title4 font=swiss color=black bcolor=CXFFFFFF "Really REALLY REALLY REALLY REALLY REALLY long title"

Title5 font=swiss color=black bcolor=CXFFFFFF "Really REALLY REALLY REALLY REALLY REALLY REALLY long title"

column

column_a

column_b

column_c

;

define column_a / display "Column A Name";

define column_b / display "Column A Name";

define column_c / display "Column A Name";

run;

ods tagsets.excelxp close;

Re: ODS PDF, Proc Report - Spanrows and Row height

Cynthia_sas — Fri, 03 Jan 2014 00:01:44 GMT

Hi:

Yes, mentioning that you were using an *entirely* different destination than the PDF destination which was the subject of the original post would have been helpful. This is just another reason why I recommend against "piggybacking" a new post onto an old post. Most people, me included, think that you have a LOT in common with the original post, (such as the destination) when that was not, in fact, the case.

In addition, are you sure your code runs? Do you get error messages in the log? For one issue, where is your PROC REPORT statement? For a second issue, your COLUMN statement seems wrong to me (I would expect to see column a b c; and not what you show:

column

column a

column b

column c

;

... and your DEFINE statements don't look correct either:

DEFINE COLUMN A / ...; instead of the usual DEFINE A / "Column A Name";

I would expect some error messages in the log from your code. The third issue is that you do not have semicolons at the end of your TITLE statements (only TITLE1) and the 4th issue is that you are using a custom style, but don't show whether the custom style does anything to redefine the TITLE attributes.

Is this your ACTUAL code, that really runs, without errors? Also, can you clarify your statement about "the title row height does not adjust for how many columns I will have" -- I do not expect the ROW HEIGHT to adjust for the number of columns you have. I expect the WIDTH allocated to the TITLES to expand based on the number of columns you have. So, if you only have 3 columns you will have a "narrower" table for the titles to span than if you have 20 columns or a "wider" table for the titles to span. Can you post a screen shot that shows exactly what the issue is. Without data and without a correct program to run, it is nearly impossible to make a constructive suggestion.

And, I also don't understand why you're even using the SWISS font for your titles. That doesn't make sense to me. SWISS was an old font that folks used to use with SAS/GRAPH, it's just not as nice as Arial or Helvetica...and there's a possibility that folks getting the spreadsheet won't have it.

Cynthia

Re: ODS PDF, Proc Report - Spanrows and Row height

bullman — Fri, 03 Jan 2014 00:21:25 GMT

Cynthia, you are correct. was coding in a rush. See above edits.

Additionally, I have opened a track with IT.

Re: ODS PDF, Proc Report - Spanrows and Row height

Cynthia_sas — Fri, 03 Jan 2014 01:45:57 GMT

... and the errors in the TITLE statements (no semi-colons)...again, does your code really run??? And does it really produce results? I'd be very interested in seeing what it looks like. For example, you only define 3 columns, but your absolute_column_width suboption is setting sizes for 8 columns.

cynthia