topic Exterior Borders on Proc Report in ODS and Base Reporting

Exterior Borders on Proc Report

NewSASPerson — Fri, 30 Jan 2015 19:52:46 GMT

I am not sure if this is possible but i would appreciate assistance. I am trying to create box around a proc report in ods tagsets. The column width has to be absolute and I do not want to wrap the text.

Basically the column with the long text will span across several other column and I would to set borders around it. Is this possible?

Please see sample code below and advise what i could modify. I have attached the desired result. Thank you

data test;

set sashelp.adomsg(keep=text);

where length(text) = 80;

run;

ods _all_ close;

ods tagsets.excelxp

file="test4.xls"

style=minimal options(absolute_column_width="15,3,3,5,5" autofit_height="yes" sheet_name="Test" sheet_interval = "none");

proc report data=test noheader box style(Report)={rules=groups frame=box borderwidth=1} ;

define text/group style(column)={ font_face='Browallia New' font_size=3 tagattr='wrap:no'

borderrightcolor=black borderrightwidth=1

bordertopcolor=white bordertopwidth=1 };

compute after _page_ / style={bordertopcolor=black bordertopwidth=1 tagattr='wrap:no' };

line ' ';

endcomp;

compute before _page_ / style={borderbottomcolor=black borderbottomwidth=1 tagattr='wrap:no'};

line ' ';

endcomp;

run;

proc report data=sashelp.class style(column header)={tagattr='wrap:no'};

run;

ods _all_ close;

Re: Exterior Borders on Proc Report

ballardw — Fri, 30 Jan 2015 20:31:41 GMT

Since you say

the long text will span across several other column

I seems to imply there may be other data involved as well. If so, you should provide an example of other data and how it is supposed to interact.

Look at Tagattr to specify behaviors after Excel gets ahold of things, such as whether a cell will wrap text.

Re: Exterior Borders on Proc Report

NewSASPerson — Fri, 30 Jan 2015 21:52:35 GMT

I have update the code to reflect the additional dataset and included desired result in excel. The tagattr does specify not to wrap text but not sure how to get just the exterior borders on the top dataset. Thank you.

Re: Exterior Borders on Proc Report

Cynthia_sas — Sat, 31 Jan 2015 15:03:38 GMT

Hi:

The issue of "borders" in Excel or a frame around the box are different than the other destinations. For example, consider the code below. I made a dataset, since I did not have SASHELP.ADOMSG then I used the JOURNAL style with a simple FRAME=BOX and you can see the results in the screen shot of the RTF file opened in Word compared to the TAGSETS.EXCELXP XML file opened in Excel. I think that the Word "look and feel" is what you're aiming for. If you run the code, you will see how each destination, HTML, PDF and RTF respects the fact that JOURNAL style doesn't have any interior lines inside the table and the FRAME=BOX does get respected by those 3 destinations. However, all bets are off with TAGSETS.EXCELXP. I did change your code a bit -- if I use WIDTH= and autofit_heights, I am pretty happy with what happens in Excel. As you can see, the other destinations automatically auto fit the height of the cell, based on the width of the cell and the contents in the cell. I took off the BOX option that you had because it is only used by the LISTING destination and no other destination. I put some explicit text in the LINE statement for before and after _PAGE_ because I wanted you to see where the BOX would be drawn, since the LINE text is "inside" the frame of the report output. Also, since each of my lines of text is unique, I just made the usage ORDER instead of GROUP and I put ORDER=DATA so they would show up in the order I typed them. I'm not sure why a length of exactly 80 was needed, but my last data line is exactly 80 characters wide/long.

I generally find it hard for SAS to specify any borders for Excel (in the procedure syntax with style overrides), Excel has a mind of its own when it comes to "gridlines" because it treats them as 2 things -- grid lines to show you when you're just looking at the sheet and grid lines that can turn on or off when you print. This paper http://www2.sas.com/proceedings/forum2008/036-2008.pdf starting on page 13/14 (although the rest of the paper is good) has an explanation of why borders in Excel with TAGSETS.EXCELXP are difficult. And the style template method shown in the paper is, I believe, the only way to control the borders at the level you want. If there is any other way, you'd have to find that out from Tech Support, but as you can see by running the example below, Excel is different from other destinations in regard to borders.

Cynthia

**make some text data that is 80 or bigger;
data test;
length text $150;
infile datalines dsd;
input text $;
return;
datalines;
"The database already exists and will be replaced. Do you want to proceed with the copy?"
"Table WOMBAT does not have any numeric variables. There is something wrong in the file"
"Twas brillig and the slithy toves did gyre and gimble in the wabe. All mimsy were the borogroves and the mome raths outgrabe."
"This is a line of text xxxxx aaaaaaaaa bbbbbb cccc that is exactly 80 chars long"
;
run;

ods _all_ close;

title;
ods html file="c:\temp\test4.html" style=journal;
ods rtf file="c:\temp\test4x.rtf" style=journal startpage=no;
ods pdf file="c:\temp\test4x.pdf" style=journal startpage=no;
ods tagsets.excelxp file="c:\temp\test4x.xml"
   style=journal options(sheet_interval='none' sheet_name='test' autofit_height="yes" );

proc report data=test noheader
style(report)={frame=box};
column text;
define text/order order=data
      style(column)={width=2in};
compute after _page_ /style={background=white};
line 'Line After';
endcomp;

compute before _page_ /style={background=white};
line 'Line Before';
endcomp;
run;

proc report data=sashelp.class(obs=3);
run;

proc report data=sashelp.class(obs=3)
      style(report)={frame=box};
run;

ods _all_ close;

Re: Exterior Borders on Proc Report

Ksharp — Sun, 01 Feb 2015 06:38:57 GMT

Since it is actullay a XML file , a workaround way is changing the source code, but that is too cumbersome .

Re: Exterior Borders on Proc Report

Cynthia_sas — Mon, 02 Feb 2015 01:26:34 GMT

And, if you make a mistake in altering the XML, you might corrupt the file, so in my mind, that is another reason not to change the XML after the file is created.

cynthia