BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Child79
Fluorite | Level 6

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.

 

ex_proctab_sst.png

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

ex_proctab_stt1.png

 

 

I will appreciate your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

4 REPLIES 4
Ksharp
Super User

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
Fluorite | Level 6

Thanks for correcting the data example step.

ballardw
Super User

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
Fluorite | Level 6

Thanks for solution. It works correctly.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 710 views
  • 2 likes
  • 3 in conversation