proc tabulate

[Ronein]

Hello

I want to make some changes to following report:

1- In Report1 - For each year (2019,2020) to add a column with Total N

2- In Report2 - For each year (2019,2020) to add a column with Total Y

3-In Report 3 I want to calculate percent from total N in each team, year.

Currently this calculation is wrong and I don't get the desired outcome.

4-In Report 4 I want to calculate percent from total Y in each team, year.
Currently this calculation is wrong and I don't get the desired outcome.

5-I want to ask if there is a way to get the 4 outputs in one code  of proc tabulate

data Rawtbl;
input ID z1 z2 z3 $ Y;
cards;
1 830 2020 a 10
2 830 2020 b 20
3 830 2019 b 30
4 830 2019 b 40
5 830 2019 b 50
6 830 2020 b 60
7 850 2019 b 70
8 850 2019 a 80
9 850 2019 a 90
10 850 2020 a 100
11 830 2019 c 110
12 830 2019 c 120
;
run;


proc format;
value $FFF
a= 'Sucess'
b,c= 'Failure'
;
run;


/***Report1*****/
/***Report1*****/
/***Report1*****/
option missing=0;
proc tabulate data=Rawtbl style=[font_face="Arial" font_size=9pt just=c];
class z1 z2 z3;
classlev z1 /style=[font_face="Arial" font_size=9pt fontweight=medium backgroundcolor=white];
classlev z2 z3 / style=[font_face="Arial" font_size=9pt];
table z1='', z2=''*z3=''*n=''/ printmiss 
                              box= [label='Team'  style=[font_face="Arial"
                                                                  font_size=9pt
                                                                  just=l
                                                                  verticalalign=bottom]];
format z3 $FFF.;
run;


/***Report2*****/
/***Report2*****/
/***Report2*****/
option missing=0;
proc tabulate data=Rawtbl style=[font_face="Arial" font_size=9pt just=c];
class z1 z2 z3;
var Y;
classlev z1 /style=[font_face="Arial" font_size=9pt fontweight=medium backgroundcolor=white];
classlev z2 z3 / style=[font_face="Arial" font_size=9pt];
table z1='', z2=''*z3=''*SUM=''*Y='Sum_Y'/ printmiss 
                              box= [label='Team'  style=[font_face="Arial"
                                                                  font_size=9pt
                                                                  just=l
                                                                  verticalalign=bottom]];
format z3 $FFF.;
run;



/***Report3*****/
/***Report3*****/
/***Report3*****/
option missing=0;
proc tabulate data=Rawtbl style=[font_face="Arial" font_size=9pt just=c];
class z1 z2 z3;
classlev z1 /style=[font_face="Arial" font_size=9pt fontweight=medium backgroundcolor=white];
classlev z2 z3 / style=[font_face="Arial" font_size=9pt];
table z1='', z2=''*z3=''*PCTN=''/ printmiss 
                              box= [label='Team'  style=[font_face="Arial"
                                                                  font_size=9pt
                                                                  just=l
                                                                  verticalalign=bottom]];
format z3 $FFF.;
run;


/***Report4*****/
/***Report4*****/
/***Report3*****/
/***Report4*****/
proc tabulate data=Rawtbl style=[font_face="Arial" font_size=9pt just=c];
class z1 z2 z3;
Var Y;
classlev z1 /style=[font_face="Arial" font_size=9pt fontweight=medium backgroundcolor=white];
classlev z2 z3 / style=[font_face="Arial" font_size=9pt];
table z1='', z2=''*z3=''*PCTSUM*Y=''/ printmiss 
                              box= [label='Team'  style=[font_face="Arial"
                                                                  font_size=9pt
                                                                  just=l
                                                                  verticalalign=bottom]];
format z3 $FFF.;
run;

[BrunoMueller]

Have look at the code sample below, I removed all the formatting stuff, it is easier to get started when you still see the table structure.

I assume in the end you want to have all the calculations in one table statement.

There are several books on Proc TABULATE as well to get more insight.

data Rawtbl;
input ID z1 z2 z3 $ Y;
cards;
1 830 2020 a 10
2 830 2020 b 20
3 830 2019 b 30
4 830 2019 b 40
5 830 2019 b 50
6 830 2020 b 60
7 850 2019 b 70
8 850 2019 a 80
9 850 2019 a 90
10 850 2020 a 100
11 830 2019 c 110
12 830 2019 c 120
;
run;


proc format;
value $FFF
a= 'Sucess'
b,c= 'Failure'
;
run;



option missing=0;
proc tabulate data=Rawtbl
/*  style=[font_face="Arial" font_size=9pt just=c]*/
;
class z1 z2 z3;
var y;
classlev z1 /;
classlev z2 z3 /;
/***Report1*****/
/***Report1*****/
/***Report1*****/
table z1 , z2 * z3*n all*N / printmiss ;


/***Report2*****/
/***Report2*****/
/***Report2*****/
table z1 , z2 *(z3 all="Total") *SUM *Y='Sum_Y'/ printmiss;

/***Report3*****/
/***Report3*****/
/***Report3*****/
table z1 , z2 * z3  * (n pctn<all*z1 z3>="pctn<all*z1 z3>"*f=4.1) / printmiss ;

/***Report4*****/
/***Report4*****/
/***Report3*****/
/***Report4*****/
table z1 , z2 * z3 *  (sum PCTSUM<all*z1 z3>="pctsum<all*z1 z3>"*f=4.1)*Y / printmiss ;

format z3 $FFF.;
run;