Tab with category as label
I am trying to create a table that contains the totals for each group. I have sections, subsections and items. After each subsection, a line with the totals of this subsection should appear, and after each section a line with the general section number should appear.
I tried to use proc tabulate
but the subtotals are simply called the sum or some other fixed label. I would like to use the category name for subtotals.
Example 1
First a small example to illustrate, below I have a complete example working which shows what I have tried with proc tablulate and what I would like to achieve.
Input data
==============================
Group Group2 Item Weight
----- ------ ---- ------
Mammals Cats Lion 215
Mammals Cats Cheetah 70
Mammals Dogs Wolf 80
Mammals Dogs Jackal 45
==============================
Desired result
===================
Group/Item Weight
----------- ------
Lion 215
Cheetah 70
Cats 285
----------- ------
Wolf 80
Jackal 45
Dogs 125
----------- ------
Mammals 410
===================
Example 2
Quite a long example of SAS code that also contains missing values
data animals;
input group1 $ group2 $ animal $ weight;
datalines;
Mammals Cats Lion 215
Mammals Cats Cheetah 70
Mammals Cats Leopard 65
Mammals Dogs Wolf 80
Mammals Dogs Jackal 45
Birds Raptors Eagle 6
Birds Raptors Hawk 5
Birds . Duck 2
. . Snake 3
;
My failed attempt, the table is too complex
PROC TABULATE DATA=ANIMALS OUT=ANIMAL_SUMMARY;
VAR weight;
CLASS group1 / ORDER=DATA MISSING;
CLASS group2 / ORDER=DATA MISSING;
CLASS animal / ORDER=DATA MISSING;
TABLE group1*(group2*(animal Sum) Sum) Sum, weight;
RUN;
Data shape close to the desired result
data target;
input group1 $ group2 $ animal $ weight;
datalines;
. . Lion 215
. . Cheetah 70
. . Leopard 65
. Cats . 350
. . Wolf 80
. . Jackal 45
. Dogs . 125
Mammals . . 475
. . Eagle 6
. . Hawk 5
. Raptors . 11
. . Duck 2
Birds . . 13
. . Snake 3
. . . 491
;
source to share
I don't think you can get exactly what you want from TABULATE. You may have to go to Report to get exactly what you want.
You can get close, however.
PROC TABULATE DATA=ANIMALS OUT=ANIMAL_SUMMARY;
VAR weight;
CLASS group1 / ORDER=DATA MISSING;
CLASS group2 / ORDER=DATA MISSING;
CLASS animal / ORDER=DATA MISSING;
TABLE group1*(group2*(animal=' ' Sum=' '*group2=' ') Sum=' '*group1=' ') Sum='Total', weight;
RUN;
I am adding here group2
and group1
and removing a bunch of shortcuts. For some reason it doesn't remove all the shortcuts, but it does remove most of them and gets your group2 / group1 on the right line.
It also gives a warning:
Warning. The class variable interferes with itself in the table statement at line 313. This can lead to unpredictable results.
That's fine in this case, but just keep in mind (especially if your store is a "blank magazine" store, in which case it may not work).
Also, I think you have to go to PROC REPORT
to get, which is perfectly possible to do (REPORT gladly makes intermediate lines with RBREAK
, for example).
source to share