topic Re: excelxp proc report: bold lines between groups in ODS and Base Reporting

excelxp proc report: bold lines between groups

gzr2mz39 — Mon, 29 Nov 2010 21:57:25 GMT

Is there a way to create bold, black lines between groups (ie variable "num")?
Also, is there a way to create a space between groups?
Thank you.

ods tagsets.excelxp file="C:\Documents and Settings\LA_Training.xls"
style=journal
options(frozen_headers='1'
autofit_height='yes'
absolute_column_width='4,15,8,15,6,15,20,20,25')
;

proc report data=traininj1 nowd contents="Claims" out=test
style(header)=[font_weight=bold];
options missing='0';
column num job_class date1 type;
define num / group 'ID' style(column)={just=l} order=data;
define job_class / 'Job' style(column)={just=l};
define date1 / 'Date';
define type / 'Class/Injury';
compute type;
if type in ("Incident Only") then call define(_row_,"style","style=[background=yellow]");
endcomp;
break after num/ ol skip;
run;

ods tagsets.excelxp close;

Re: excelxp proc report: bold lines between groups

Cynthia_sas — Tue, 30 Nov 2010 01:19:57 GMT

Hi:
PROC REPORT options like OL and SKIP are only designed for the LISTING destination and, as you have seen, have no impact on HTML, RTF, PDF or TAGSETS.EXCELXP destinations.

However, to put the equivalent of a skip between groups, if NUM is your GROUP or ORDER item, you can do this:
[pre]
compute after num;
line ' ';
endcomp;
[/pre]

And the LINE statement will put the equivalent of a skipped line between each unique value of NUM.

Also, you should probably change the usage of NUM to ORDER or else change the usage of JOB_CLASS to either ORDER or GROUP, so you can avoid this message in the log:
[pre]
NOTE: Groups are not created because the usage of job_class is DISPLAY. To avoid this note,
change all GROUP variables to ORDER variables.
[/pre]

As far as changing the interior table lines, the JOURNAL style, normally, does not have any interior table lines. So the concept of re-creating the overline (OL) at the break is not something that is easily done in the Excel XML.

For HTML, the "workaround" to simulate an overline is shown in this Tech Support set of examples: http://support.sas.com/rnd/base/ods/templateFAQ/report1.html

But Excel does not respect the HTMLSTYLE method. So that leaves you with fiddling with the border top/border bottom style elements. The style element that controls the LINE output is the NoteContent style element. In the program below, I modify the NoteContent style element to have a border top of solid black and to have a background and foreground color of pink. These may not be the colors you want, but you can see what gets changed. That change is done in the style template, to show the type of change you can make at the template level for the LINE output.

Without the SUMMARIZE option, you do not get any summary numbers -- so in my program the WEIGHT variable is a DISPLAY usage, so it is not summarized, while the HEIGHT variable is a SUM usage, so it will be summarized. The style of the SUMMARY line is changed in a style override to alter the bordertopstyle and bordertopwidth, etc.

Between these 2 techniques, you should be able to simulate the OL and the SKIP for TAGSETS.EXCELXP output. This worked for me in SAS 9.2 with Excel 2010.

cynthia
[pre]

ods path work.tmp(update)
sasuser.templat(update)
sashelp.tmplmst(read);

proc template;
define style styles.myjour;
parent=styles.journal;
class NoteContent /
foreground=pink
background=pink
bordertopstyle=solid
bordertopcolor=black
bordertopwidth=2;
end;
run;

ods tagsets.excelxp file="C:\temp\LA_Training.xls"
style=styles.myjour
options(doc='Help');

options missing='0';
ods listing close;
proc report data=sashelp.class nowd contents="Claims"
out=test
style(summary)=Header{foreground=purple background=cxeeeeee
bordertopwidth=2 borderbottomwidth=0
bordertopcolor=cyan borderbottomcolor=_undef_
bordertopstyle=solid borderbottomstyle=_undef_};
column age sex name weight height;
define age / order 'ID' style(column)={just=l};
define sex / display 'Job' style(column)={just=l};
define name / display 'Date';
define weight / display 'Class/Injury';
define height /sum;
break after age / summarize ;
compute sex;
if sex eq 'F' then
call define(_row_,"style","style=[background=yellow]");
endcomp;

compute after age;
line ' ';
endcomp;
run;

ods tagsets.excelxp close;
[/pre]

Re: excelxp proc report: bold lines between groups

Ksharp — Tue, 30 Nov 2010 02:27:26 GMT

Just to add something into Cynthia's code.
[pre]

ods tagsets.excelxp file="C:\temp\LA.xls"
style=listing
options(doc='Help');

options missing='0';
ods listing close;
ods escapechar='~';
proc report data=sashelp.class nowd contents="Claims"
out=test
style(summary)=Header{foreground=purple background=cxeeeeee
bordertopwidth=2 borderbottomwidth=0
bordertopcolor=cyan borderbottomcolor=_undef_
bordertopstyle=solid borderbottomstyle=_undef_};
column age sex name weight height;
define age / order 'ID' style(column)={just=l};
define sex / display 'Job' style(column)={just=l};
define name / display 'Date';
define weight / display 'Class/Injury';
define height /sum;
break after age / summarize ;
compute sex;
if sex eq 'F' then
call define(_row_,"style","style=[background=yellow]");
endcomp;

compute after age;
str=repeat('=',44) ;
line "~S={font_weight=bold} " str $44.;
line ' ';
endcomp;
run;

ods tagsets.excelxp close;
ods listing;

[/pre]

And hope Cynthia can use ODS ESCAPECHAR to make the underline blod.

Ksharp

Message was edited by: Ksharp

Re: excelxp proc report: bold lines between groups

Cynthia_sas — Tue, 30 Nov 2010 05:49:22 GMT

Hi,
Sorry, using repeat to write 44 equal signs seems sort of "LISTING-ish" to me and it is not a technique that I would use with any non-LISTING destination. Besides, the OP was using OL, so I don't see where the equal signs are needed.

Besides BORDERTOPSTYLE seems to work with TAGSETS.EXCELXP and Excel, For a finer level of control, each column could have the bordertopstyle turned on or off as needed. If you want to use LISTING techniques, then OL and SKIP, etc will work just fine -- in the LISTING window.

ODS RTF, PDF, HTML or TAGSETS.EXCELXP --are just not the same as the LISTING window and, personally, I would not spend too much time working on trying to replicate a LISTING look and feel.

Cynthia

Re: excelxp proc report: bold lines between groups

gzr2mz39 — Tue, 30 Nov 2010 20:31:27 GMT

I like this idea:
compute after num; line ' ';endcomp;
I will look into the proc template idea.
Thank you.