"SubTotal" Using Compute after Block -- I Only get First 3 Letters of Word?

[MikeCa]

I'm using the following proc report code trying to insert "SubTotal" verbiage by utilizing break after and compute after block but I only get the first 3 letters of "Sub Total", see code below? How do I get around this? Is it because my original data is only 3 chars?



Partial Output
EXCa EXCm DESC OKC-1
105 105.01 Cash 1000.00
105 105.02 Cash2 1050.00
Tot 2050.00


SAS Code
dm 'log' clear;
dm 'odsresults' clear;

* Tells SAS where to find the ExcelXP tagset;
ods tagsets.excelxp file="I:\FINANCE\QShare\2010\Stmt201010s\GLTrialBal.xml" style=minimal
options(sheet_name='GLTrialBal'
sheet_interval='proc'
frozen_headers='1'
autofit_height='yes'
absolute_column_width='5,4,25,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9'
autofilter='yes');

proc report data=GLBAL NOWD
style(header)={ BACKGROUND = yellow
FONT_WEIGHT = bold}
style(column)={background = CXFFFFFF};
COLUMN EXCm EXCa DESC b1 b11 qbo b9 b2 b3 b62 b63 b67 qbs qbt b12 b27 b14 b25 bqet b16 b76 b78 bqgm b75 bal b77 b79;
define EXCm / order;
define EXCa / order;
define b1 / 'OKC-1' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b11 / 'QURC-11' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define qbo / 'OKC Total' style(column)={background = CXD1E5C7 tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b9 / 'Tulsa-9' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b2 / 'Denver-2' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b3 / 'Admin-3' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b62 / 'Pinedale-62' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b63 / 'Uinta-63' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b67 / 'Uinta-67' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define qbs / 'Uinta Total' style(column)={background = CXD1E5C7 tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define qbt / 'QEP Total' style(column)={background = CXE8D898 tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b12 / 'QMR-12' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b27 / 'OSI-27' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b14 / 'OET-14' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b25 / 'Clr Crk-25' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define bqet / 'QET Total' style(column)={background = CXE8D898 tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b16 / 'OGM-16' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b76 / 'RGS-76' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b78 / 'RdzvPipe-78' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define bqgm / 'QGM Total' style(column)={background = CXE8D898 tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b75 / 'Wexpro' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define bal / 'QMR Total' style(column)={background = CXE8D898 tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b77 / 'UBFS-77' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
define b79 / '3RG-79' style(column)={tagattr='format:#,##0.00_);[Red](#,##0.00)'};
Break after EXCm / summarize summarize style(summary)={font_weight=bold background = CXFFFF00};;
compute after EXCm;
EXCm="Total="||EXCm;
endcomp;

rbreak after / summarize summarize style(summary)={font_weight=bold background = CXFFFF00};
compute after;
EXCm="Grand Total";
endcomp;

compute EXCm;
if EXCm NE ' ' then hold=EXCm;
if EXCm EQ ' ' then EXCm=hold;
endcomp;

run;
ods tagsets.ExcelXP close;

[Cynthia_sas]

Hi:
If the EXCM variable is a character variable with a length of $3, then you are correct that the length of the string that you can assign at a break is limited by the length of the variable width. There are a couple of different things you can do.
1) change the length of EXCM outside of the PROC REPORT step
2) create a computed variable version of EXCM that is wider
3) make a user-defined format that contains the string you want
4) use the PRETEXT=/POSTTEXT= style attribute to put a string on the total

The code below uses SASHELP.CLASS and illustrates these 4 methods. (To run the code, since it has been posted with the "fixed pitch font" tag, you will need to cut and paste from the forum window into an application that respects line feeds (like Word) and then cut and paste from Word into your SAS code window.)

cynthia
[pre]
** Default behavior: SEX variable has a length of $1;
ods html file='c:\temp\default.html' style=sasweb;
proc report data=sashelp.class nowd;
column sex name age;
define sex / order;
define name / order;
define age / mean;
rbreak after / summarize;
compute after;
sex = 'Average Age';
endcomp;
run;
ods html close;

** 1) change the length of the variable outside of the PROC REPORT step;
data class;
length sex $15;
set sashelp.class;
run;

ods html file='c:\temp\method1.html' style=sasweb;
proc report data=class nowd;
column sex name age;
define sex / order;
define name / order;
define age / mean;
rbreak after / summarize;
compute after;
sex = 'Average Age';
endcomp;
run;
ods html close;

** 2) create a computed variable version of the variable that is wider;

ods html file='c:\temp\method2.html' style=sasweb;
proc report data=sashelp.class nowd;
column sex displaysex name age;
define sex / order noprint;
define displaysex / computed;
define name / order;
define age / mean;
rbreak after / summarize;
compute displaysex / character length=15;
displaysex = sex;
endcomp;
compute after;
displaysex = 'Average Age';
endcomp;
run;
ods html close;

** 3) make a user-defined format that contains the string you want;
proc format;
value $fmts 'M' = 'Male'
'F' = 'Female'
other = 'Average Age';
run;

ods html file='c:\temp\method3.html' style=sasweb;
proc report data=sashelp.class nowd;
column sex name age;
define sex / order f=$fmts.;
define name / order;
define age / mean;
rbreak after / summarize;
compute after;
sex = put(' ',$fmts.);
endcomp;
run;
ods html close;

** 4) use the PRETEXT=/POSTTEXT= style attribute to put a string on the total;

ods html file='c:\temp\method4.html' style=sasweb;
proc report data=sashelp.class nowd;
column sex name age;
define sex / order;
define name / order;
define age / mean;
rbreak after / summarize;
compute sex;
if _break_ = '_RBREAK_' then
call define(_col_,'style','style={pretext="Average Age"}');
endcomp;
run;
ods html close;

[/pre]

[MikeCa]

Thankyou Cynthia! I love your examples in your responses; always right on the mark! Mike