Hi: You did not use the code I posted when using the C_TIER variable. For C_TIER, you do NOT need to use the PRETEXT method. But now that you have included the extra information about Excel, the PRETEXT method that you're using now will ONLY work for SubTotal, it will NOT work for Grand Total, as illustrated in #1 in the program below.
You have a different issue once you move to Excel. The techniques I posted work fine in "regular" ODS destinations, but when you are using ODS Excel, to create output, Excel has a default way to treat a number in a cell -- you made C_TIER as a character string in SAS with a length of 11, but there is only a number in the cell -- so basically Excel ignores the SAS variable type and wants to treat that cell as a number. Which is fine for the subtotals, which do have the TIER value in them. But on the Grand Total line, there is no value at all, no number. So Excel ignores everything.
There is a workaround when you are wanting your output to go to Excel. It is shown in example #2.
In the future, when you post a question, it is ALWAYS important to know, up front, what ODS destinations you are going to use for your report. The destination you use might have an impact on the answer, as shown in this case.
cynthia
Output #1 in Excel shows that Grand Total does NOT work with PRETEXT method in ODS EXCEL
Output #2 in Excel shows that simple assignment statement works (do NOT need PRETEXT), but must represent TIER values as Tier 0, Tier 1, etc
The code is below. NOTE that there are 2 different forms of TIER used in each program. #1 uses C_Tier and #2 uses C_Tier_Alt:
** original TIER is numeric;
** make a character version of TIER as C_TIER;
** make a second version of TIER as C_TIER_ALT;
data test1;
length C_tier c_tier_alt $11;
input year Areacode country $ tier sales;
c_tier = put(tier,1.0);
c_tier_alt = catx(' ','Tier',put(tier,1.0));
datalines;
2017 1 US 1 100
2017 1 US 2 200
2017 1 US 3 300
2017 1 US 4 120
2017 1 US 5 450
2017 1 US 0 390
2017 2 US 0 100
2017 2 US 1 300
2017 2 US 2 60
2017 2 US 3 1000
2017 3 US 0 200
2017 3 US 1 220
2017 3 US 2 140
2017 1 US 4 390
2017 2 US 5 100
2017 2 US 3 300
2017 2 US 4 60
2017 2 US 3 1000
2017 3 US 5 200
2017 3 US 1 220
2017 3 US 4 140
;
run;
title; footnote;
ods excel file='c:\temp\st_gt.xlsx';
ods excel options(sheet_name='1_not_work');
** 1) will ONLY work for SubTotal in Excel;
proc report data=test1
style(summary)=Header;
where tier le 2;
columns year Areacode country c_tier sales;
define year/display "Year of Sale";
define Areacode/display "Area";
define country/ display "Country";
define c_tier/order "Tier" style(column)={just=r};
define sales/ analysis sum "Total Sales";
break after c_Tier /summarize dol dul;
rbreak after/summarize;
compute after c_tier;
call define('c_tier','style','style=Header{pretext="SubTotal " tagattr="Type:String"}');
endcomp;
compute after;
call define('c_tier','style','style=Header{pretext="Grand Total " tagattr="Type:String"}');
endcomp;
run;
ods excel options(sheet_name='2_will_work');
** 2) if tier is character do not need CALL DEFINE;
proc report data=test1
style(summary)=Header;
where tier le 2;
columns year Areacode country c_tier_alt sales;
define year/display "Year of Sale";
define Areacode/display "Area";
define country/ display "Country";
define c_tier_alt/order "Tier" style(column)={just=r};
define sales/ analysis sum "Total Sales";
break after c_Tier_alt /summarize dol dul;
rbreak after/summarize;
compute after c_tier_alt;
c_tier_alt = 'SubTotal';
endcomp;
compute after;
c_tier_alt = 'Grand Total';
endcomp;
run;
ods excel close;
title;
It would have been useful to know you wanted Excel output at the beginning, you'll have to use a different Technique with Excel. The problem is that even if you make c_tier with a PUT function in SAS, Excel still wants to treat the column as a number when you get the report to Excel. And, while there is a number in the cell at the break for the subtotal line, there is not a number or anything for PRETEXT to be used with on the Grand total report line. So Excel will not use PRETEXT.
It will take me a few minutes to modify the program for ODS EXCEL. It's going to requre that you actually put the value as Tier 0, Tier 1, Tier 2, etc into the cell for TIER. cynthia
... View more
