BookmarkSubscribeRSS Feed
KimLeBouton
Quartz | Level 8
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
8 REPLIES 8
Cynthia_sas
SAS Super FREQ
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
Tourmaline | Level 20
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
SAS Super FREQ
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
Tourmaline | Level 20
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
SAS Super FREQ
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
Tourmaline | Level 20
So many ways to skin a cat! Beautiful. Thanks Cynthia.
Cynthia_sas
SAS Super FREQ
Hi:
Although I have to say that no animals were harmed in the production of the report!
cynthia
KimLeBouton
Quartz | Level 8
Thanks for both solutions. More than likely, I'll use both throughout the years to come.

Kim

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1209 views
  • 0 likes
  • 3 in conversation