ExcelXP - cannot print borders on last row of table without summarizing values

rjroland — Wed, 14 Oct 2015 22:06:23 GMT

Hello,

I am trying to do the following:

1. Create a table using ExcelXP

2. Table outer borders=2 and inner borders=1 Without using the rbreak after/summarize option

I can't use the summarize option because the rows in my dataset have overlap (e.g. 1 row is all of US, the next row is only Hawaii,..) so summarizing the values would be wrong. The part I am really stuck on is how do you add a line to the bottom of the table similar to how rbreak after/summarize allows you to do without actually printing out a summary of values.

I am using SAS EG version 9.02.02M3P041310

The following code shows the format of the table I want except for two problems:

1. I don't want to print a summary row of data at the end

2. The summary entry by rbreak after/summarize has an extra border at the bottom right due to having to code a thick border on the right border of the table in my cell entry

Is there any way to get around this? A few options I have kicked around seem less than perfect:

1. Use rbreak after/summarize and then use a compute after statement to zero out all of the fields, but I still get the straggler borders in the summary when I code field level borders

2. Use a table border of thickness 2 and then for each field, check and make sure that it isn't the first or last record of the dataset. If it isn't, then override the border of every field to use borderbottom and bordertop of 1. This way, the bottom and top of the table still have a border of 2 but interior has a border of 1. This seems like an ok solution but wanted to see if there was something better.

I have included 2 files, one is the code I pasted below and referenced above and the other is a comparison of what SAS spits out and what I wanted it to look like.

Thanks!

Raj

Code below

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

proc template;
Define style styles.custom; /*Defines new style named "custom"*/

Parent=styles.journal; /*determines parent style to borrow qualities from*/

style systitleandfootercontainer from systitleandfootercontainer/borderwidth=0;
Style table from table /background=transparent borderwidth=1 bordercolor=black;
style header from header /borderwidth=2 bordercolor=black;
end;
run;

** show that template works with TABULATE and REPORT;
ods tagsets.excelxp options(embedded_titles='yes' embedded_footnotes='no' gridlines='yes')
file="test_TableFormat.xml"
style=styles.custom;

Title1 BOLD J=LEFT "TEST FORMATS";

proc report data=sashelp.class Missing Spacing=2 nowindows split='*';
column height weight

('General Statistics'
('Age' age)
('Demographics' height_out weight_out)
);

define name / group 'Student*Name' left ;
define sex / group 'Gender' right ;
define age / group 'Age';
define height / analysis noprint;
define weight / analysis noprint;
define height_out / computed 'Height*(inches)' center;
define weight_out / computed 'Weight*(pounds)' style(column)={borderrightcolor=black borderrightwidth=2 bordertopcolor=black bordertopwidth=1} center;

Compute height_out; height_out=height.sum;EndComp;
Compute weight_out; weight_out=weight.sum;EndComp;

rbreak after / summarize
style={
font_weight=bold bordertopwidth=2 bordertopcolor=black};

/*I want to get rid of rbreak, but then I won't get a solid border at the bottom of my table*/
run; quit;

ods tagsets.excelxp close;

ods listing;

Re: ExcelXP - cannot print borders on last row of table without summarizing values

SASKiwi — Wed, 14 Oct 2015 23:13:03 GMT

Are you using the latest Excelxp tagset? In your SAS log you will see a note like this:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 08/02/2013). Add options(doc='help') to the ods statement for more information.

If you are not using the latest version V1.130 download it from here: http://support.sas.com/rnd/base/ods/odsmarkup/index.html

and see if that fixes your problem.

Re: ExcelXP - cannot print borders on last row of table without summarizing values

rjroland — Thu, 15 Oct 2015 10:02:25 GMT

Thanks Saskiwi, I currently have the following version: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12).

After the updated version is installed, is there a recommended method to get what I had listed, which is a different set of borders for the table vs. the interior gridlines of the table without using the rbreak after/summarize option?

Thanks,

Raj

Re: ExcelXP - cannot print borders on last row of table without summarizing values

SASKiwi — Thu, 15 Oct 2015 18:48:20 GMT

I would try the existing code first with the updated tagset just to see if there is any difference.

topic Re: ExcelXP - cannot print borders on last row of table without summarizing values in ODS and Base Reporting

ExcelXP - cannot print borders on last row of table without summarizing values

Re: ExcelXP - cannot print borders on last row of table without summarizing values

Re: ExcelXP - cannot print borders on last row of table without summarizing values

Re: ExcelXP - cannot print borders on last row of table without summarizing values