topic proc report-add 3 total columns in SAS Programming

proc report-add 3 total columns

Ronein — Thu, 18 Jun 2020 12:00:54 GMT

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;

Re: proc report-add 3 total columns

ed_sas_member — Thu, 18 Jun 2020 12:54:13 GMT

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,

Re: proc report-add 3 total columns

ballardw — Thu, 18 Jun 2020 14:09:10 GMT

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

Re: proc report-add 3 total columns

Ronein — Sun, 21 Jun 2020 05:09:02 GMT

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;