How to Label totals and subtotals in PROC Report?

[Gangi]

I am trying to give some name in the box highlighted in the image, (for Grand Total and Subtotals)

im using the below code to produce the report,

Please help me.

proc report data=abc OUT=test;
columns YEAR1 OFFER_MONTH CNTRY_CD
Term Tier TOTAL_OFFERS;
define year1/ display "YEAR";
define OFFER_MONTH/display "OFFER MONTH";
define CNTRY_CD/ display "COUNTRY";
define Tier/group "Tier";
define TOTAL_OFFERS/ analysis sum "Sales" format=comma8.;
break after Tier /summarize dol dul;
Compute after ;
type="Grand Total :";
endcomp;
run;

[Cynthia_sas]

Hi: What you show is an Enterprise Guide data viewer look at the dataset created by PROC REPORT. Any text will be written to the actual report itself. What does your report output look like in the result viewer.

However, another problem that I see is that your COMPUTE block is changing the value of the TYPE variable or item, but I do not see TYPE in your COLUMN statement and the columns you have highlighted belong to the Term and Tier columns, not the TYPE column.

Also, no one can run you code, since you have not provided any data for them to use.

cynthia

[Gangi]

Thank you very much for quick reply, 

code and images are below 

data test1;
input year Areacode country $ tier sales;
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;

proc report data=test1;
columns year Areacode country tier sales;
define year/display "Year of Sale";
define Areacode/display "Area";
define country/ display "Country";
define Tier/group "Tier";
define sales/ analysis sum "Total Sales";
break after Tier /summarize dol dul;
rbreak after/summarize;

run;

as highlighted below, I am trying  to give name for every subtotal and also grand total,

please suggest me how I can achieve this?
run;

[Cynthia_sas]

Hi:

  You have two choices, depending on whether the TIER variable is character or numeric. Either way, you need a COMPUTE block. If the TIER variable was character, you would only need a simple COMPUTE block with an assignment statement, as shown in #2. But, if TIER is numeric, then you need to use a style override and use the PRETEXT style attribute to put a character string into a numeric cell. Code is below. Note that I changed your DATA step program to make the C_Tier variable for #2. (If TIER would be more than 1 digit, you would need to change the PUT statement accordingly.) In my test, I used a WHERE statement to restrict the rows on the report -- you would not do that for your final report.

Cynthia

** original TIER is numeric;
** make a character version of TIER as C_TIER;
data test1;
length C_tier $11;
input year Areacode country $ tier sales;
c_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;

** 1) if tier is numeric;
proc report data=test1 
     style(summary)=Header;
where tier le 2;
columns year Areacode country tier sales;
define year/display "Year of Sale";
define Areacode/display "Area";
define country/ display "Country";
define Tier/group "Tier";
define sales/ analysis sum "Total Sales";
break after Tier /summarize dol dul;
rbreak after/summarize;
compute after tier;
  call define('tier','style','style=Header{pretext="SubTotal"}');
endcomp;
compute after;
  call define('tier','style','style=Header{pretext="Grand Total"}');
endcomp;
run;

** 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 sales;
define year/display "Year of Sale";
define Areacode/display "Area";
define country/ display "Country";
define c_tier/group "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;
  c_tier = 'SubTotal';
endcomp;
compute after;
  c_tier = 'Grand Total';
endcomp;
run;

 (corrected step 2 to use c_tier. I accidentally posted incorrect code for step 2)

[Gangi]

Thank you very much for your reply, suggested code works superbly, but I have got one more issue,

when I try to download that as excel using proc report the grandtotal coming as blank cell rest everything looks good,

could you please suggest me how I can achieve this?

Thank you very much..

data test1;
length C_tier $11;
input year Areacode country $ tier sales;
c_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;

ods excel file= "C:\Users\abc\Desktop\bca.xlsx";
proc report data=test1
style(summary)=Header;

columns year Areacode country c_tier sales;
define year/display "Year of Sale";
define Areacode/display "Area";
define country/ display "Country";
define c_tier/group "Tier";
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"}');
endcomp;
compute after;
call define('c_tier','style','style=Header{pretext="Grand Total"}');
endcomp;
run;

ods excel close;

 

[Cynthia_sas · Accepted solution]

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