ODS EXCELXP.TAGSET & PROC REPORT Customized RBREAK

[KimLeBouton]

Is there any "easy" way to get "Overall Totals" to print for the RBREAK line with ODS EXCELXP.TAGSET? See PDF report for the "Overall Totals" RBREAK.

ods listing close;
ods html close;

ods pdf file="c:\temp\modified rbreak.pdf";

ods tagsets.excelxp file="c:\temp\modified rbreak.xls";

ods tagsets.excelxp
options(sheet_name='Summary');

proc report data=sashelp.prdsale nowd;
title "Modified Total";
where product='SOFA';
col country region predict actual division prodtype product ;

compute country;
if _BREAK_='_RBREAK_' then
call define(_COL_,'STYLE','STYLE=[FONT_STYLE=italic PRETEXT="Overall Totals"]');
endcomp;

rbreak after / summarize;
run;

ods tagsets.excelxp close;
ods pdf close;

Thanks so much,
Kim

[Cynthia_sas]

Hi, Kim:
PRETEXT=, as you discovered, doesn't work in all destinations the same way. But, with a regular COMPUTE block, you can accomplish the same end result...just a different path. Your string "Overall Totals" is longer than the assigned variable length of COUNTRY in SASHELP.PRDSALE ($10), so that's problematic-- but can be taken care of with a COMPUTED report item. PRETEXT is a nice workaround sometimes for that, but apparently, not with ExcelXP.

So, in my code, PRTCTRY will be displayed instead of COUNTRY...same test for _RBREAK_, only on a different report item. Then, I put the italic on the RBREAK statement, because it was more straightforward to put it there than on a call define, which was no longer needed for PRETEXT.

cynthia
[pre]

ods pdf file="c:\temp\modified_rbreak2.pdf";

ods tagsets.excelxp file="c:\temp\modified_rbreak2.xls";

ods tagsets.excelxp
options(sheet_name='Summary');

proc report data=sashelp.prdsale nowd;
title "Modified Total";
where product='SOFA';
col country prtctry region predict actual division prodtype product ;
define country / noprint;
define prtctry / computed 'Country';
compute prtctry / character length=15;
prtctry = country;
if _BREAK_='_RBREAK_' then do;
prtctry="Overall Totals";
end;
endcomp;
rbreak after / summarize
style={font_style=italic};
run;

ods _all_ close;

[/pre]

[ChrisNZ]

This works. Unsure why your version doesn't, or why the width= option is ignored.
[pre]
ods listing close;
ods html close;

ods pdf file="f:\modified rbreak.pdf";
ods tagsets.excelxp file="f:\modified rbreak.xls";
ods tagsets.excelxp options(sheet_name='Summary');

data prdsale/view=prdsale;
attrib country length=$50 format=$50.;
set sashelp.prdsale;
run;
proc report data=prdsale nowd;
title "Modified Total";
where product='SOFA';
column country region predict actual division prodtype product ;
*define country/format=$50. width=50;
compute country;
if _BREAK_='_RBREAK_' then do;
country="Overall Totals";
call define(ROW,'STYLE','STYLE=[FONT_STYLE=italic]');
end;
endcomp;
rbreak after / summarize;
run;

ods tagsets.excelxp close;
ods pdf close;

[Cynthia_sas]

Hi:
WIDTH= is a LISTING only option. Also, for listing format can also control column width...but that is not true of other ODS destinations.


There are a couple of ways to do what Kim wanted.

I picked what I thought was easiest. To assign the string to the value for country at the break to COUNTRY, you have to be sure that the variable length (not the format) is wide enough for the entire string.

Cynthia

[ChrisNZ]

Cheers Cynthia.
Yes, I discovered that the variable length (not the format length) had to be increased, hence the view.
I provided an alternative to show that creating an extra variable wasn't required, but then my length was to short for the label and I had to create a view... oh well... 🙂

[Cynthia_sas]

Hi:
Creating a new report item isn't that much overhead. PROC REPORT is very good at creating COMPUTED report items that only exist for the duration of the PROC REPORT step. The original variable on the report is unchanged in the data and is used in the creation of the COMPUTED report item.

As I said, there are other possible solutions. Another is to use a user-defined format.

cynthia
[pre]
proc format;
value $ctry 'CANADA' = 'Canada'
'U.S.A.' = 'United States'
'GERMANY' = 'Germany'
'xxx' = 'Overall Totals';

ods pdf file="c:\temp\modified_rbreak3.pdf";
ods tagsets.excelxp file="c:\temp\modified_rbreak3.xls";

ods tagsets.excelxp
options(sheet_name='Summary');

proc report data=sashelp.prdsale nowd;
title "Modified Total";
where product='SOFA';
col country region predict actual division prodtype product ;
define country / f=$ctry.;
compute country ;
if _BREAK_='_RBREAK_' then do;
country="xxx";
end;
endcomp;
rbreak after / summarize
style={font_style=italic};
run;

ods _all_ close;
[/pre]

[ChrisNZ]

So many ways to skin a cat! Beautiful. Thanks Cynthia.

[Cynthia_sas]

Hi:
Although I have to say that no animals were harmed in the production of the report!
cynthia

[KimLeBouton]

Thanks for both solutions. More than likely, I'll use both throughout the years to come.

Kim