proc report-add 3 total columns

[Ronein]

Hello

I want to add  4 more total columns.

Total Nr customers for  Z3=No

Total Nr customers for  Z3=Yes

Sum income  for  Z3=No
Sum income  for  Z3=Yes

I am also happy to hear about any suggestion how to make the table better or display it better

data ttt;
input ID Z1 $ Z2 $ Z3 $ Z4 $ Y;
cards;
1 a express Yes Long 10
2 a express Yes Long 20
3 a express No Long 30
4 a Direct No Long 40
5 a Direct Yes Long 150
6 a Direct No Long 60	

7 a express Yes short 15
8 b express Yes short 25
9 b express No short 35
10 b Direct No short 45
11 b Direct Yes short 55
12 b Direct No short 65
;
run;
Proc tabulate data =ttt  format=comma21.;
Class z1  z2  z3  z4  ;
Var y;
Table z1=""*z2='',
z3='Nr customers'*z4=''*N='N'   all='income'*N='Grand total N'
z3='Sum Income'*z4=''*SUM=''*y='SUM'  all='Sum Income'*y='Grand total income'
/box='Z1/Z2' misstext='0'  nocellmerge;
Run;

[ed_sas_member]

Hi @Ronein 

What if you add the following code to the TABLES statement?

	Table z1=""*z2='' all, /*added:all*/
		z3='Nr customers'*z4*N
		all='income'*N='Grand total N'
		z3='Sum Income'*z4*y
		all='Sum Income'*y='Grand total income'
		z3*N /*added*/
		z3*y /*added*/

Best,

[ballardw · Accepted solution]

One way

Proc tabulate data =ttt  format=comma21.;
   Class z1  z2  z3  z4  ;
   Var y;
   Table z1=""*z2='',
         z3='Nr customers'*(z4='' All)*N='N'   
         all='income'*N='Grand total N'
         z3='Sum Income'* (z4='' All)*SUM=''*y='SUM'  
         all='Sum Income'*y='Grand total income'
   /box='Z1/Z2' misstext='0'  nocellmerge;
Run;

When you want  an "all" for each level of a class variable then typically you go to the next level nested variable for the All.  So   Z3 *(Z4 All)  gets the summary for all levels of Z4 within each level of Z3.

Personally since you have provided reasonable column headings indicating Number of Customers and Total Income I would suppress the statistic label unless you intend to add another statistic like "mean income" within each level of Z4

[Ronein]

Thank you so much!!

I have a  question please regarding the great code that you provided:

 1-Column "Grand Total N " has one column header  

  Column  "Grand Total sum income" has 2 columns headers : "Grand Total sum income" and below of it  "SUM".

What is the way that both columns will have same number of headers?

Proc tabulate data =ttt  format=comma21.;
   Class z1  z2  z3  z4  ;
   Var y;
   Table z1=""*z2='',
         z3='Nr customers'*(z4='' All)*N='N'   
         all=''*N='Grand total N'
         z3='Sum Income'* (z4='' All)*SUM=''*y='SUM'  
         all=''*y='Grand total sum income'
   /box='Z1/Z2' misstext='0'  nocellmerge;
Run;