topic Re: ExcelXP: specifying borders on specific cells in ODS and Base Reporting

ExcelXP: specifying borders on specific cells

deleted_user — Tue, 11 Nov 2008 00:03:30 GMT

I'm using the most recent version of ExcelXP to create spreadsheets. I have some particular cells I want to add a bottom border two. I figured out how to use compute blocks to change the formatting on those cells but not how to change borders.

I would like to know how to modify the formatting below to include a bottom border for each of these cells. I tried htmlstyle= but that didn't work. Any clues?

compute ttl_svngs;
if rptline=50 then call define(_col_,'style','style=[background=silver font_weight=bold]');
else if rptline=23 then call define(_col_,'style','style=[font_weight=bold]');
endcomp;

Re: ExcelXP: specifying borders on specific cells

Eric_SAS — Tue, 11 Nov 2008 16:08:09 GMT

Hello Anita,

Add

borderbottomstyle=solid borderbottomwidth=2 borderbottomcolor=black

to your style. Of course you can use other values, although Excel doesn't do much with
width, there is really only 1-4 for that.

The border style can have a lot of settings. The ODS style is on the left, the corresponding Excel style is on the right. Excel doesn't have as many as ODS does.
Use the ODS style in your over rides, it is case insensitive.

DOTTED "Dot";
DASHED "Dash";
SOLID "Continuous";
DOUBLE "Double";
GROOVE "DashDot";
RIDGE "DashDotDot";
INSET "SlashDashDot";
OUTSET "Continuous";
HIDDEN "None";

Re: ExcelXP: specifying borders on specific cells

deleted_user — Tue, 11 Nov 2008 16:35:57 GMT

Thanks Eric,

I tried adding borderbottomstyle= etc to the style in my call define but I got the error message "ERROR 22-322: Syntax error, expecting one of the following: ;, ABSTRACT, ACTIVELINKCOLOR, ASIS, BACKGROUND, BACKGROUNDIMAGE, BODYSCROLLBAR, BODYSIZE,..." It doesn't know those particular attributes.

I know how to make my own style with proc template but I can't figure out how to apply that style in a call define.

Re: ExcelXP: specifying borders on specific cells

Eric_SAS — Wed, 12 Nov 2008 15:16:09 GMT

Ok. I remember that was a problem in SAS 9.1.3. The extra border top, bottom, right and left
style attributes did not work as style over rides.

They do work in a proc template style. So if you create a style element with those attributes
you can use that style element as your over ride.

Proc template;

style styles.mystyle;
parent=styles.Journal;

define bottom_data from data/
borderbottomstyle=solid
borderbottomwidth=2
borderbottomcolor=black
;
end;
run;

proc report ....

call define .... /style=bottom_data[ other over rides here ];

Re: ExcelXP: specifying borders on specific cells

Cynthia_sas — Wed, 12 Nov 2008 15:29:20 GMT

Hi: Some of the style attributes may be SAS 9.2 attributes. I think there were some of these borderxxx attributes that were pre-prodution for RTF only. (http://support.sas.com/rnd/base/ods/odsrtf/rtf901.html) So they might not work for other destinations if you're not at SAS 9.2.

Also, the other thing that you could be running into is the PLACE where you're trying to use the attribute is not an OK place. For example, you are trying to use the attribute on the PROC REPORT statement, but the attribute can only be used on the DEFINE or RBREAK (and other statements) inside Proc Report. In a CALL DEFINE statement, you should be able to alter attributes that are CELL related (but not TABLE related). In other words, a CALL DEFINE for a particular column or calculated report item would not be able to alter the style for the -whole- table.

To find out which situation you're running into, your best bet for help might be to contact SAS Tech Support.

Also, here are some useful links from the 9.2 doc:
http://support.sas.com/documentation/cdl/en/odsug/59523/HTML/default/a002972093.htm
http://support.sas.com/documentation/cdl/en/odsug/59523/HTML/default/a002972093.htm#a001047947
http://support.sas.com/resources/papers/stylesinprocs.pdf

cynthia

Re: ExcelXP: specifying borders on specific cells

Eric_SAS — Wed, 12 Nov 2008 18:12:01 GMT

I wrote a paper last year that, among other things, shows how to do borders in Excel with proc report.

The very last part of the paper uses a SAS 9.2 feature of proc report, style/merge.
But everything else is applicable to SAS 9.1.3.

Specifically, the use of the border attributes by creating a style template instead of using them
in the over rides which doesn't work.

http://www2.sas.com/proceedings/forum2008/036-2008.pdf

The HTML destination also supports the border attributes.