Subtotals in rows and columns in proc tabulate SAS

[Child79]

Hello,

Here is description of my datasets

data students;

input id:$4 town:$5  Sex:$6   date:$2. var1:8. var2:8.  var3:8. ;

datalines;

id1    Paris Female    T0   2 5 8

id2    Paris Female   T1   1 8 2

id3    Paris Female   T0   3 4 6

id4    Paris Female   T1   6 2 9

id5    Paris male       T0    1 1 2

id6    Paris male       T1    3 5 1

id7    Paris male       T0    4 7 8

id8    Paris male       T1   2 8 3

id9    rome Female   T0   4 5 2

id10  rome Female   T1  5 2  8

id11  rome Female   T0  2 9 3

id12  rome Female   T1  3 7 3

id13  rome male       T0   5 5 2

id14  rome male       T1   6 3 9

id15  rome male      T0  3 7 7

id16  rome male       T1  5 7 8

;

  I want to create this table with tabulate procedures.

I used this code

proc tabulate data=students;
var var1 var2 var3;
class date sex town;
table
/* row statement */
town=" " * (var1 *sum var2 * sum  var3 * sum) ,
/* column statement */
date*(sex all="sous total ");
;
run;

My code works but the problem is that I don't get the subtotals in rows.

But I obtain this table

I will appreciate your help.

[Ksharp · Accepted solution]

You need create a NEW variable.

data students;
input id $ town $  Sex $   date $  var1:8. var2:8.  var3:8. ;
datalines;
id1    Paris Female    T0   2 5 8
id2    Paris Female   T1   1 8 2
id3    Paris Female   T0   3 4 6
id4    Paris Female   T1   6 2 9
id5    Paris male       T0    1 1 2
id6    Paris male       T1    3 5 1
id7    Paris male       T0    4 7 8
id8    Paris male       T1   2 8 3
id9    rome Female   T0   4 5 2
id10  rome Female   T1  5 2  8
id11  rome Female   T0  2 9 3
id12  rome Female   T1  3 7 3
id13  rome male       T0   5 5 2
id14  rome male       T1   6 3 9
id15  rome male      T0  3 7 7
id16  rome male       T1  5 7 8
;

data students;
 set students;
total=sum(var1,var2,var3);
run;
proc tabulate data=students;
var var1 var2 var3 total;
class date sex town;
table
/* row statement */
town=" "*(var1 var2   var3  total)*sum='' ,
/* column statement */
date*(sex all="sous total ");
;
label total='sous total ';
run;

[Child79]

Thanks for correcting the data example step.

[ballardw]

Tabulate basically does not combine multiple variables.

Another approach is reshape your data set so instead of Var1 Var2 and Var3 you have another variable that indicates the Name of the variable to use as Class variable one Var variable to summarize:

Proc transpose data=students out=need;
   by id town sex date notsorted;
   var var1-var3;
run;

proc tabulate data=need;
   class  town sex date _name_;
   var col1;
   table
   /* row statement */
   town=" " * (_name_=" " all='Sous total'),
   /* column statement */
   date=" "*(sex=" " all='Sous total')*col1=' '*Sum=' '*f=best6.
   ;
run;

Please make sure that example data steps run. Your Input statement was missing . for informat names and a ; to end the data lines. Also it is a good idea to paste the code into a text box opened on the forum with the </> icon that appears above the main message window. The main windows reformat text and can do so in a manner that the data step may not run.

[Child79]

Thanks for solution. It works correctly.