topic Re: Subtotals in rows and columns in proc tabulate SAS in SAS Programming

Subtotals in rows and columns in proc tabulate SAS

Child79 — Sat, 01 Oct 2022 22:11:52 GMT

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.

Re: Subtotals in rows and columns in proc tabulate SAS

Ksharp — Sun, 02 Oct 2022 10:49:15 GMT

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;

Re: Subtotals in rows and columns in proc tabulate SAS

ballardw — Sun, 02 Oct 2022 11:41:50 GMT

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.

Re: Subtotals in rows and columns in proc tabulate SAS

Child79 — Mon, 03 Oct 2022 15:41:42 GMT

Thanks for correcting the data example step.

Re: Subtotals in rows and columns in proc tabulate SAS

Child79 — Mon, 03 Oct 2022 15:44:16 GMT

Thanks for solution. It works correctly.