sas - Tabulate With Category as Label -

i trying produce table contains totals each group. have sections, subsections , items. after each subsection line totals of subsection should appear , after each section line section total.

i tried using proc tabulate subtotals called sum or fixed label. use category name subtotals.

example 1

first small example illustrate, below have complete running example shows tried proc tablulate , 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

a slighly longer example in sas code includes 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 unsuccessful attempt, table complicated

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; 

a form of data close 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 ; 

i don't think can exactly want tabulate. you'd have go report want.

you can close, though.

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'm adding group2 , group1 here, , removing bunch of labels. doesn't remove of labels reason, remove of them, , gets group2/group1 on right row.

it gives warning:

warning: class variable crossed in table statement @ line 313. may cause unpredictable results.

that's fine, in case, aware of (particularly if shop 'clean log' shop; may not work in case).

further think you'd have go proc report get, quite possible (report happy subtotal rows rbreak example).