The SAS Output Delivery System and reporting techniques

ExcelXP - controlling borders - not like other ods destinations?

Reply
SAS Employee
Posts: 8

ExcelXP - controlling borders - not like other ods destinations?

Hi All,

Before I loose all of my hair to frustration, maybe you can help me.

The following sample code demonstrates my issue. I want to create a report (much like ye olde listings), that have a line after the title, a line after the column headings, no lines within the data and a line at the bottom of the report before the footnotes.

I've tried a lot of things, including the frame= and rules= (as shown in a PDF paper) and some of the border options to no avail.

Basically, from the code below, I'd like to be able to remove the underlines in the Age column, and want to get a line under the title and one before the footer.

I know I can do this in proc report easier, but the customer has literally hundreds of proc tabulate reports that they want to automate the formatting of and output to multisheet excel workbooks (thus the excelxp choice).

The code is:

proc template;

define style bottomline;
parent = styles.journal;

style table from table /
borderwidth=0
cellspacing=0;

style header from header /
borderbottomwidth=1
;
end;
run;

ods tagsets.excelxp options(embedded_titles='yes' embedded_footnotes='yes')
file="c:\temp\bottomline.xml" style=bottomline;
title "title";
proc tabulate data=sashelp.class;
class age sex;
table age='Age',
sex*n='' n='Total'*all='';
run;
ods tagsets.excelxp close;

Any help would be greatly appreciated.

thanks
John
SAS Super FREQ
Posts: 8,869

Re: ExcelXP - controlling borders - not like other ods destinations?

Posted in reply to johnc_sas
Hi:
What you describe sounds a lot like the JOURNAL style output for other destinations, but for TAGSETS.EXCELXP, you might have to put a bit more work into the design of your style template.

This paper has an excellent discussion of borders with TAGSETS.EXCELXP, starting on page 14:
http://www2.sas.com/proceedings/forum2008/036-2008.pdf

where it says (underlining for emphasis):
"Unfortunately borders are difficult to deal with because Excel doesn't have the concept of a table like HTML or RTF or even LaTeX. In Excel the only thing that can have borders is an individual cell. This complicates things quite a bit. At one time the ExcelXP tagset used the table style for the entire worksheet. That worked okay, but frequently gave the wrong background colors and caused the cell outlines to show through out the entire worksheet.".

And, even better, the paper has a custom style template that shows the type of controls you need to use instead of setting your border controls on the table style element.

cynthia
SAS Employee
Posts: 8

Re: ExcelXP - controlling borders - not like other ods destinations?

Posted in reply to Cynthia_sas
Hi Cynthia,

Thanks for the response. Actually, I had already downloaded Eric's example code that produce the screenshots he used in the paper you've linked to. I spent a lot of time playing around with the various options, but am unable to produce the results I want. Indeed you are on the money with regards to the Journal style, as this is pretty much exactly what I want to produce in Excel.

I find you statement "In Excel the only thing that can have borders is an individual cell" worrying, because from my code 2nd sample all cells in the crosstab are treated as header cells (e.g. I'm getting the borders at the side, which look ugly). I think it comes down to being able to figure out what tags relate to what cells, and if indeed what I am looking for is actually possible through ExcelXP.

I tried the MSOffice2k destination, and in the html page all looks good, but the formatting changes when I open it in excel.

The documentation on line seems a bit patchy in relation to identifying exactly what tags I need to look at specifically for ExcelXP. The standard docs say they are valid in markup destinations, but this does not translate into reality (given the same template will produce different results in ExcelXP versus say MSOfficeXP).

Other than papers such as Eric's which give good examples of some of the tags, I can't resolve this without knowing if there are other sub tags that will allow me to remove the underlining at the side. My gut says it must be possible.

thanks again
John
SAS Super FREQ
Posts: 8,869

Re: ExcelXP - controlling borders - not like other ods destinations?

Posted in reply to johnc_sas
Hi:
It is not the tags that you need to understand to do what you want to do. You need to understand the style ELEMENTS and ATTRIBUTES that need to be applied in the style template to get the results you want.

You say you are using PROC TABULATE. Any variable listed on a CLASS or VAR statement will be controlled by either the HEADER style element or the ROWHEADER style element. CLASS variables specified in the ROW dimension in TABULATE will be styled by the ROWHEADER element, but it inherits from HEADER -- so you may have to style them differently if you want your lines to be different in the ROWHEADER area. PROC REPORT, on the other hand, would not use the ROWHEADER element to style values for GROUP or ORDER variables -- It would just use the HEADER element for the COLUMN headers and the DATA element for the cell columns that went down the rows.

You did not say whether you were using 9.1 or 9.2. I can't remember whether that paper was written using the 9.1 style syntax or the 9.2 style syntax. However, these 2 examples should get you started.

In the first example, I made a style template that works in TAGSETS.EXCELXP to start slowly and just get rid of ALL the borders and interior table lines. That's the first step. Then, once that is working, I go back and add border lines in a second template. Note that my PROC TEMPLATE syntax uses the new CLASS statement, as described in the doc and in my paper about Style templates:
http://support.sas.com/resources/papers/proceedings10/033-2010.pdf

Because there is an implicit parent template (Base.Template.Style) to control inheritance, if I use the CLASS statement, I do not need to use a "FROM" in my syntax. Also, I don't need to use a parent template, at all. I can define a style template "from scratch" -- now there are a few elements missing, like SystemTitle and or ProcTitle that you may want to add back in -- or switch to using a parent template, but overall, I like to keep things as simple as possible when figuring out style elements.

cynthia
[pre]
ods path work.tmp(update) sashelp.tmplmst(read);

** 1) Noborder at all and no explicit parent template;
proc template;
define style styles.noborder;
class table /
borderwidth=0
rules=none
frame=void
cellspacing=0
background=white;
end;
run;

** show that template works with TABULATE and REPORT;
ods tagsets.excelxp options(embedded_titles='yes' embedded_footnotes='yes')
file="c:\temp\noborder2x.xml" style=styles.noborder;

proc tabulate data=sashelp.class;
title "1 title TABULATE";
class age sex;
table age='Age',
sex*n='' n='Total'*all='';
run;

proc report data=sashelp.class nowd;
title '1 title REPORT';
column age n,sex age=all;
define age / group;
define sex / across;
define n / ' ';
define all / n 'Total';
run;

ods tagsets.excelxp close;


** 2) Now add borders;
** Noborder at all and no explicit parent template;
proc template;
define style styles.hdrborder;
class table /
borderwidth=0
rules=none
frame=void
cellspacing=0
background=white;

class header /
borderbottomwidth=2
borderbottomstyle=solid
borderbottomcolor=black
;

class rowheader /
borderbottomwidth=0
borderbottomstyle=solid
borderbottomcolor=white
borderrightwidth=2
borderrightstyle=solid
borderrightcolor=black
;

end;
run;


ods tagsets.excelxp options(embedded_titles='yes' embedded_footnotes='yes')
file="c:\temp\hdrborderx.xml" style=styles.hdrborder;

proc tabulate data=sashelp.class;
title "2 title TABULATE";
class age sex;
table age='Age',
sex*n='' n='Total'*all='';
run;


** In PROC REPORT, the AGE variable is not styled by the ROWHEADER element;
** so a few style overrides are needed.;
proc report data=sashelp.class nowd;
title '2 title REPORT';
column age n,sex age=all;
define age / group
style(header)=header
style(column)=rowheader{just=c};
define sex / across;
define n / ' ';
define all / n 'Total';
run;

ods tagsets.excelxp close;


** 3) Now use styles.journal as a parent template;
proc template;
define style styles.journal_border;
parent=styles.journal;
class table /
borderwidth=0
rules=none
frame=void
cellspacing=0
background=white;

class header /
borderbottomwidth=2
borderbottomstyle=solid
borderbottomcolor=black
;

class rowheader /
borderbottomwidth=0
borderbottomstyle=solid
borderbottomcolor=white
borderrightwidth=2
borderrightstyle=solid
borderrightcolor=black
;

end;
run;

ods tagsets.excelxp options(embedded_titles='yes' embedded_footnotes='yes')
file="c:\temp\jborder.xml" style=styles.journal_border;

proc tabulate data=sashelp.class;
title "3 title TABULATE";
class age sex;
table age='Age',
sex*n='' n='Total'*all='';
run;


** In PROC REPORT, the AGE variable is not styled by the ROWHEADER element;
** so a few style overrides are needed.;
proc report data=sashelp.class nowd;
title '3 title REPORT';
column age n,sex age=all;
define age / group
style(header)=header
style(column)=rowheader{just=c};
define sex / across;
define n / ' ';
define all / n 'Total';
run;

ods tagsets.excelxp close;
[/pre]
SAS Employee
Posts: 8

Re: ExcelXP - controlling borders - not like other ods destinations?

Posted in reply to Cynthia_sas
Wow, thanks a million for your comprehensive response Cynthia. I had been going down the wrong route, and was using attempting to use the "rules=" option without success and with much frustration. (9.2 btw).

It seems ods to tagsets.excelxp does indeed behave differently to say rtf, and with trial and error, and a large degree of patience, it is possible to get close to ye-olde journal style.

Many thanks again

John
SAS Super FREQ
Posts: 8,869

Re: ExcelXP - controlling borders - not like other ods destinations?

Posted in reply to johnc_sas
Hi:
Well, I'm a sort of "old school curmudgeon" on this point. RTF was designed to interact with a Word processor -- when you put your report or your laundry list or your great American novel in RTF form (for a word processor), the underlying assumption is that you want to cosmetically use RTF control strings (or word processor controls) to get the document ready for the prom (printing, taking to the store, sending to a publisher). And RTF has all kinds of ways to control the cosmetics of the document so that the document looks good when formatted (or when the underlying document is dressed up in the prom dress).

A spreadsheet is NOT a word processing document -- no matter how pretty you can make it -- a spreadsheet is data and you can put the data in a prom dress, but the prom dress is going to need a lot of alteration so it really looks good on the dance floor. A word processing document only does one thing -- it sits there being a document -- it looks good in a prom dress because the Word document is all about how the document looks -- whether the "information" in the document is a laundry list or Finnegan's Wake. A spreadsheet is data -- you are asking it to do double duty when you want the data in the tabular spreadsheet to suddenly have cosmetics and interior table lines that look "just so".

It seems to me that when I go into Excel and make a spreadsheet from scratch that it is sort of a pain to manually alter the borders of the cells in the table if I want to do anything other than turn on all the borders or turn off all the borders. It is always a pain for me to figure out whether I want the top of one row to have a border or the bottom of another row to have a border. That is always a trial and error process in manual mode and requires a lot of patience.

So, when you say that using style templates with TAGSETS.EXCELXP needs trial and error and a lot of patience, I am thinking to myself -- the reason it is trial and error, whether you're in manual mode in Excel or using SAS code is because the underlying structure of the spreadsheet is different from the underlying structure of a DOCUMENT. My grandmother used to say something about silk purses...

cynthia
N/A
Posts: 0

Re: ExcelXP - controlling borders - not like other ods destinations?

Posted in reply to Cynthia_sas
My question is related to the borders in XL and I am replying to this post itself.
In XL trying to get borders only for header and summaries. First Proc template with options to get no borders and then Journal style in excelXP and finally in proc report with style on summary as below code. Still not sure, where exactly I can fix this to get top and bottom borders for the summary parts in excel?

style(summary)=[fontsize=2 textalign=r fontweight = bold bordertopcolor = black borderbottomcolor = red];

Thanks in advance for your support.
SAS Super FREQ
Posts: 8,869

Re: ExcelXP - controlling borders - not like other ods destinations?

Posted in reply to deleted_user
Hi:
I almost didn't see this. In the future, it might be better to start a new post and then just put a link to the post you want to "build on".

If you are going to use a custom style to get rid of the interior table lines for output from TAGSETS.EXCELXP, then you may as well add a new style element to the template -- in this template, I call my custom element SUMHEADER -- I know that there is no SAS procedure that will ever ask for SUMHEADER, so once the custom style element is in the template, I just need to refer to that style element in the PROC REPORT code. While most usages of PROC REPORT the curly braces and the style attribute overrides, PROC REPORT also supports a reference to a style element (in which case, no curly braces are needed.)

The example below builds on the examples above and uses JOURNAL as the parent and then customizes the TABLE and HEADER elements, but adds the SUMHEADER element.

cynthia
[pre]
ods path work.tmp(update) sashelp.tmplmst(read);
** 4) Now use styles.journal as a parent template;
** and create a custom element called SUMHEADER;
** that will be used for summary lines in PROC REPORT;
proc template;
define style styles.journal_sum;
parent=styles.journal;
class table /
borderwidth=0
rules=none
frame=void
cellspacing=0
background=white;

class header /
fontweight=bold
fontsize=10pt
font_face='Arial'
borderbottomwidth=2
borderbottomstyle=solid
borderbottomcolor=black
;

class sumheader from header/
fontweight=bold
textalign=r
fontsize=10pt
font_face='Arial'
borderbottomwidth=2
borderbottomstyle=solid
borderbottomcolor=red
bordertopwidth=2
bordertopstyle=solid
bordertopcolor=black
;
end;
run;

ods listing close;
ods tagsets.excelxp file='c:\temp\summ_bord.xls' style=styles.journal_sum;

** In PROC REPORT, the AGE variable is not styled by the ROWHEADER element;
** so a few style overrides are needed.;
proc report data=sashelp.class nowd;
title 'title REPORT';
column age n,sex age=all;
define age / group
style(column)={fontweight=bold just=c};
define sex / across;
define n / ' ';
define all / n 'Total';
rbreak after / summarize style=sumheader;
run;

ods _all_ close;
[/pre]
Ask a Question
Discussion stats
  • 7 replies
  • 2982 views
  • 0 likes
  • 3 in conversation