getting proc report spanrows to work correctly with long lines of text in PDF output

[mpx]

Hi all, I am having trouble getting ODS PDF output to look correct when I am using proc report with the spanrows option.  I am currently using SAS 9.4 but I have observed this problem in SAS 9.2 as well.

Specifically when the column of data that I wish to span rows is a long line, which must be wrapped in order to fit within the specified cell width, it becomes very problematic to display it correctly.

There is a SAS knowledgebase article that references this problem: 45549 - Wrapped text strings in the first column cause unwanted white space in subsequent rows in PR...

There it suggests setting the cell height to a small value in order to "force" the row spacing to be correct.  However, I want to point out that this does not work properly in cases where the rest of the data rows which this item spans do not "naturally" create enough room to display the wrapped text in the cell that should be spanning the rows.

Below see some example code illustrating the problem.  I have two small datasets (tmp1, tmp2), and I generate 2 PDF tables from each of them, using proc report.  You can run the example code or look at the attached picture where I show the tables produced in each case.  The basic problem is that prior to using the "cellheight" fix, table 1 looks OK but table 2 does not.  This is what the KB article is trying to fix.  But, see the next two tables where I have used the "cellheight" fix.  Now table 3 does look like a "fixed" version of table 2.  But table 4 (which is just table 1 done with the "cellgheight" fix) does NOT look OK any more.  It incorrectly chops off the part of the long line of text that should wrap.

Can anyone give me the magic incantation that would get both tables to look right simultaneously?

Thank you in advance.

Code Example:

data tmp1 tmp2;

   length spanning_col $200 grouped_data $20;

   spanning_col = "This is a very long line that I want to word wrap";

   grouped_data = "A";

   output tmp1 tmp2;

   grouped_data = "B";

   output tmp2;

   grouped_data = "C";

   output tmp2;

run;

ods results off;

ods listing close;

ods pdf file="abc.pdf" uniform pdftoc=2;

/* with no cellheight specified, the  first table looks OK but the second does not */

ods proclabel="TMP1 report / NO CELLHEIGHT";

proc report data=tmp1 nowindows missing spanrows;

   columns spanning_col grouped_data;

   define spanning_col             / "COL1" order style={asis=on cellwidth=15%};

   define grouped_data             / "COL2";

run;

quit;

ods proclabel="TMP2 report / NO CELLHEIGHT";

proc report data=tmp2 nowindows missing spanrows;

   columns spanning_col grouped_data;

   define spanning_col             / "COL1" order style={asis=on cellwidth=15%};

   define grouped_data             / "COL2";

run;

quit;

/* with cellheight specified per the SAS KB article, the first table now looks wrong but

* the second table looks OK now. */

ods proclabel="TMP2 report with cellheight";

proc report data=tmp2 nowindows missing spanrows;

   columns spanning_col grouped_data;

   define spanning_col             / "COL1" order style={asis=on cellheight=0.1in cellwidth=15%};

   define grouped_data             / "COL2";

run;

quit;

ods proclabel="TMP1 report with cellheight";

proc report data=tmp1 nowindows missing spanrows;

   columns spanning_col grouped_data;

   define spanning_col             / "COL1" order style={asis=on cellheight=0.1in cellwidth=15%};

   define grouped_data             / "COL2";

run;

quit;

ods pdf close;

[ballardw]

I would be tempted to try switching to proc tabulate, possibly presummarizing data depending on other calculations. The tabulate nesting of class variables creates a similar behavior as spanrows.

How does this look to you?

proc tabulate data=tmp2;

class spanning_col;

class grouped_data;

table spanning_col='Col 1'*grouped_data='Col 2',n;

run;

Unfortunately tabulate will want to create at least one statistic so may not work for your actual need.

[Cynthia_sas]

Hi:

  Since you are pointing out a scenario where the workaround in the Tech Support note does NOT work, then my suggestion is that you open a track with Tech Support. Clearly, in their note, they thought the workaround would work, but since you have data and a scenario that doesn't work as advertised, the only way to bring this anomaly to the attention of the developers is for you to open a track with Tech Support. Your data and code can provide a test example for use toward a possible, updated workaround.

cynthia

[mpx]

Thanks.  I was hoping this was already known to people and that there was simply a set of options that I was not aware of.  I am very happy to open a track with Tech Support on this issue if that is really what is required.  Sorry for the very newbie question but how does one do that? 

Also, if anyone else DOES know of a workaround that does the right thing in all cases PLEASE let me know!

Thanks.

[Cynthia_sas]

Look at the bottom of this page. in tiny letters, you should see this sentence:

Community postings can contain untested user-supplied content. This content is provided as-is without warranty by SAS. For official SAS support, submit a problem report. Copyright © SAS Institute Inc. All Rights Reserved.

The words "submit a problem report" are a link. Or, alternately, go to support.sas.com and look on the left-hand navigation page under support and there should be a link there to contact Tech Support. OR send mail to support@sas.com. You can copy and paste the link to this thread in your track so they can see the problem as you have stated it. If they need more information from you, they will contact you directly after you open the track.

cynthia

[mpx]

Thank you, I appreciate it

[Ksharp]

I don't know if the following would fit your requirement .

data tmp1 tmp2;

   length spanning_col $200 grouped_data $20;

   spanning_col = "This is a very  long line that  I  want to  word wrap";

   grouped_data = "A ~3n";

   output tmp1 tmp2;

   grouped_data = "B ~3n";

   output tmp2;

   grouped_data = "C ~3n";

   output tmp2;

run;

ods escapechar='~';

ods results off;

ods listing close;

ods pdf file="abc.pdf" uniform pdftoc=2;

/* with no cellheight specified, the  first table looks OK but the second does not */

ods proclabel="TMP1 report / NO CELLHEIGHT";

proc report data=tmp1 nowindows missing spanrows;

   columns spanning_col grouped_data;

   define spanning_col             / "COL1" order style={asis=on cellwidth=15%};

   define grouped_data             / "COL2";

run;

quit;

ods proclabel="TMP2 report / NO CELLHEIGHT";

proc report data=tmp2 nowindows missing spanrows;

   columns spanning_col grouped_data;

   define spanning_col             / "COL1" order style={asis=on cellwidth=15%};

   define grouped_data             / "COL2";

run;

quit;

/* with cellheight specified per the SAS KB article, the first table now looks wrong but

* the second table looks OK now. */

ods proclabel="TMP2 report with cellheight";

proc report data=tmp2 nowindows missing spanrows;

   columns spanning_col grouped_data;

   define spanning_col             / "COL1" order style={ cellheight=0.1in cellwidth=15%};

   define grouped_data             / "COL2";

run;

quit;

ods proclabel="TMP1 report with cellheight";

proc report data=tmp1 nowindows missing spanrows;

   columns spanning_col grouped_data;

   define spanning_col             / "COL1"   order style={ cellheight=0.1in cellwidth=15%};

   define grouped_data             / "COL2";

run;

quit;

ods pdf close;

Xia Keshan

[mpx]

Thanks.  This evens out the row heights but adds a lot of space even to rows that don't need it, so I don't think it's an ideal solution.  Also in my real situation, it would be hard to dynamically figure out how long the line of text will be and insert the proper number of ~3n characters... maybe if I could figure out how to get this to work dynamically I would be close to a solution though.  This would lead down a pretty complicated path in general though.  It would be nice if SAS would just fix the problem or offer a work-around that actually does work.

[Cynthia_sas]

Hi:

  The only way that things get fixed is by opening a track with Tech Support. Since you found a scenario where the posted workaround did NOT work, the only way to trigger any additional investigation or effort toward a solution is for you (the customer) to open a track with Tech Support, so that they can work on the problem and consult with the developer.

cynthia

[mpx]

Yes, you told me to do this already, which I did.  Their response is not very helpful:

This is in regards to the PROC REPORT issue you reported with SPANROWS in PDF output in 7611480950.  This is a known defect that development plans to address in a future release of SAS.  I have added your tracking number to that defect.

I will be happy to continue working with you should you have any

follow-up questions regarding this matter. The track will remain open

for an additional 7 days to allow you an opportunity to request further

assistance on this issue. I encourage you to respond to let me know if I

have resolved this issue to your satisfaction.

I have followed up and asked if there really isn't any good solution in the mean time, but I have not heard back.  So while I wait for them to fix this in a future SAS release, I still have some dwindling hope that someone here might have a creative solution for this problem. 

Thanks!

[Ksharp]

Can you set a proper CELL Height ? they are the same thing.

/* with cellheight specified per the SAS KB article, the first table now looks wrong but

* the second table looks OK now. */

ods proclabel="TMP2 report with cellheight";

proc report data=tmp2 nowindows missing spanrows;

   columns spanning_col grouped_data;

   define spanning_col             / "COL1" order style={ cellheight=0.1in cellwidth=15%};

   define grouped_data             / "COL2";

run;

quit;

ods proclabel="TMP1 report with cellheight";

proc report data=tmp1 nowindows missing spanrows;

   columns spanning_col grouped_data;

   define spanning_col             / "COL1"   order style={ cellheight=0.4in cellwidth=15%};

   define grouped_data             / "COL2";

run;

quit;

ods pdf close;

Xia Keshan

[mpx]

Thanks I appreciate your continued interest in helping here.

The real problem for me with both of your approaches is that you need to know the cell height before generating the table, so that it can be hard coded into the proc report step.

In my real application I have a piece of SAS code that is supposed to run and generate a PDF file based on data that is dynamically refreshed every day, having text fields of variable lengths that need to fit into cells in the report, and I won't be able to guess the length of these fields in advance to hard code them into the SAS script -- it all needs to be driven from the data.  It is an automated process, and I can't  be reaching into the code every day and fiddling with it until it looks right.  That is why I was hoping that the PDF generation step could just take care of the proper sizing of the cells, as it should be able to.

Given that this bug in proc report with PDF destination isn't going to be fixed soon, and there is no convenient workaround, I either need to accept the badly formatted tables or else come up with a way to dynamically figure out how high each cell ought to be to fit all the wrapped text, and apply the proper sizing to each row.  I'd need to do this dynamically within SAS code in a data step or something.  I can imagine how the code would look, but the logic would be very complex and annoying to write involving counting characters, finding word boundaries, figuring our where the lines would wrap, and then comparing that with how much space you can anticipate having given the lengths of other "grouped" fields later on in each row... I'm sure this code wouldn't be robust 100% of the time.

This kind of thing is what you're supposed to rely on proc report itself to figure out  (for instance, if I were using RTF instead of PDF I am told this just works).

anyhow thanks again.  I do appreciate it!