topic Re: Lines Wrapping in ODS Excel in SAS Programming

Lines Wrapping in ODS Excel

superbibi — Thu, 16 May 2019 20:44:41 GMT

Hi, I want to add footnotes in the table as new lines and wondering how I can wrap lines in accordance with the cell width setting?

Thanks.



data forglm(keep=person gender y1-y4)
formixed(keep=person gender visit y);
input person gender$ y1-y4;
output forglm;
y=y1; visit=1; output formixed;
y=y2; visit=2; output formixed;
y=y3; visit=3; output formixed;
y=y4; visit=4; output formixed;
datalines;
1 F 21.0 20.0 21.5 23.0
2 F 21.0 21.5 24.0 25.5
3 F 20.5 24.0 24.5 26.0
4 F 23.5 24.5 25.0 26.5
5 F 21.5 23.0 22.5 23.5
6 F 20.0 21.0 21.0 22.5
7 F 21.5 22.5 23.0 25.0
8 F 23.0 23.0 23.5 24.0
9 F 20.0 21.0 22.0 21.5
10 F 16.5 19.0 19.0 19.5
11 F 24.5 25.0 28.0 28.0
12 M 26.0 25.0 29.0 31.0
13 M 21.5 22.5 23.0 26.5
14 M 23.0 22.5 24.0 27.5
15 M 25.5 27.5 26.5 27.0
16 M 20.0 23.5 22.5 26.0
17 M 24.5 25.5 27.0 28.5
18 M 22.0 22.0 24.5 26.5
19 M 24.0 21.5 24.5 25.5
20 M 23.0 20.5 31.0 26.0
21 M 27.5 28.0 31.0 31.5
22 M 23.0 23.0 23.5 25.0
23 M 21.5 23.5 24.0 28.0
24 M 17.0 24.5 26.0 29.5
25 M 22.5 25.5 25.5 26.0
26 M 23.0 24.5 26.0 30.0
27 M 22.0 21.5 23.5 25.0
;


proc report data = formixed;
column person visit;

define person/ display CENTER
                 style(column)=[cellwidth=0.5in];
                 
define visit/ display CENTER
                 style(column)=[cellwidth=0.5in];
                                  
 compute after _page_/ style={just=left};

    line "long long long long long long long long long line";
  endcomp;

run;

Re: Lines Wrapping in ODS Excel

ballardw — Thu, 16 May 2019 21:53:33 GMT

@superbibi wrote:

Hi, I want to add footnotes in the table as new lines and wondering how I can wrap lines in accordance with the cell width setting?

Thanks.



data forglm(keep=person gender y1-y4)
formixed(keep=person gender visit y);
input person gender$ y1-y4;
output forglm;
y=y1; visit=1; output formixed;
y=y2; visit=2; output formixed;
y=y3; visit=3; output formixed;
y=y4; visit=4; output formixed;
datalines;
1 F 21.0 20.0 21.5 23.0
2 F 21.0 21.5 24.0 25.5
3 F 20.5 24.0 24.5 26.0
4 F 23.5 24.5 25.0 26.5
5 F 21.5 23.0 22.5 23.5
6 F 20.0 21.0 21.0 22.5
7 F 21.5 22.5 23.0 25.0
8 F 23.0 23.0 23.5 24.0
9 F 20.0 21.0 22.0 21.5
10 F 16.5 19.0 19.0 19.5
11 F 24.5 25.0 28.0 28.0
12 M 26.0 25.0 29.0 31.0
13 M 21.5 22.5 23.0 26.5
14 M 23.0 22.5 24.0 27.5
15 M 25.5 27.5 26.5 27.0
16 M 20.0 23.5 22.5 26.0
17 M 24.5 25.5 27.0 28.5
18 M 22.0 22.0 24.5 26.5
19 M 24.0 21.5 24.5 25.5
20 M 23.0 20.5 31.0 26.0
21 M 27.5 28.0 31.0 31.5
22 M 23.0 23.0 23.5 25.0
23 M 21.5 23.5 24.0 28.0
24 M 17.0 24.5 26.0 29.5
25 M 22.5 25.5 25.5 26.0
26 M 23.0 24.5 26.0 30.0
27 M 22.0 21.5 23.5 25.0
;


proc report data = formixed;
column person visit;

define person/ display CENTER
                 style(column)=[cellwidth=0.5in];
                 
define visit/ display CENTER
                 style(column)=[cellwidth=0.5in];
                                  
 compute after _page_/ style={just=left};

    line "long long long long long long long long long line";
  endcomp;

run;

So which cell is this supposed to align with?

Re: Lines Wrapping in ODS Excel

Cynthia_sas — Thu, 16 May 2019 22:02:31 GMT

Hi, I'm not sure what you mean. The cellwidth for a LINE statement text is the same as the width of the entire table because the output from a LINE statement spans the entire table. Where do you want the line to wrap? When I run your code, this is what I see (at the top):

But, as you see in my second example, there are 2 methods to use the LINE statement and get the code to "wrap" to a certain point. Method 1 using ODS ESCAPECHAR (yellow highlight) and Method 2 uses multiple LINE statements (pink highlight).

Notice how the LINE statement text stays within the "boundary" of the table. This is not the same as Footnote statement behavior. A footnote statement would be outside the boundary of the table and in paged destinations like PDF and RTF would be at the bottom of the page.

Cynthia

Re: Lines Wrapping in ODS Excel

superbibi — Fri, 17 May 2019 15:28:10 GMT

Thank you for the response. I would like to align with the widths of the two columns.

Re: Lines Wrapping in ODS Excel

superbibi — Fri, 17 May 2019 15:29:43 GMT

Thank you for much for your suggestions. Is there a way that the text wraps according to the widths of the two columns?

Re: Lines Wrapping in ODS Excel

Cynthia_sas — Fri, 17 May 2019 19:15:49 GMT

Honestly, I don't understand what you mean, the text from a LINE statement spans the entire table -- so it spans BOTH columns in your example. There's not a way to control the LINE statement and treat it like it has 2 cells (which is what it sounds like you want).

You did not show your ODS statements, so I'm not sure what ODS EXCEL options you might be trying to use in addition to your PROC REPORT code.

There is another way to do it with Break processing, but it's more complicated and the issue is that since you've limited your Person column to only .5 in, the line is going to wrap very badly, like this:

if you use the break method.

So that does not seem useful.

Cynthia

Re: Lines Wrapping in ODS Excel

ballardw — Fri, 17 May 2019 20:08:45 GMT

@Cynthia_sas

I am starting to think that perhaps @superbibi is not clearly stating the issue quite clearly. I think the complaint is that a long line of text will make the two data columns wider than desired, as specified with cell widths, while the long line extends the table width to fit text as a single line. At least in HTML. I didn't try any other destinations.

Re: Lines Wrapping in ODS Excel

Cynthia_sas — Fri, 17 May 2019 22:20:57 GMT

Hi:

Well, in HTML it does look like the line is stretching the table. I posted HTML output. I did NOT post any ODS EXCEL output because the OP did NOT provide any guidance on what ODS EXCEL options/suboptions might be in effect.

When I run my code to Excel, the line wraps across both cells and both cells look what I would expect for .5in:

as you can see, the 3 LINE statements from my initial code do wrap within the widths of the 2 columns -- here's the output WITHOUT any line statements in Excel:

So it looks to me like the LINE statement is NOT causing the same issue in ODS EXCEL as in ODS HTML.

and with just the ORIGINAL LINE statement in the mix, I see this:

Again, in Excel, the cell widths seem consistent across all forms of LINE or not LINE statements in the code.

Cynthia