You don't seem to have any grouping variables in your example data that would allow for calculating subtotals and totals. So first let's just make some so we have something to demonstrate with.
data expect ;
input group subgroup id name :$20. age sub1 sub2 sub3;
cards;
1 1 1 abc 13 60 50 60
1 1 2 cde 13 70 50 66
1 1 . sub_total . 130 100 126
1 . . total . 130 100 126
2 1 3 def 14 66 71 63
2 1 4 poe 15 99 36 41
2 1 . sub_total . 165 107 104
2 . . total . 295 207 230
3 1 5 wec 16 44 66 65
3 1 . sub_total . 44 66 65
3 . . total . 44 66 65
. . . grand_total . 339 273 295
;
Now we can just throw out the summary lines to get some example INPUT data that we can use the test our program.
data have ;
set expect ;
where age^=. ;
run;
Now if you want to do this with PROC SQL then just UNION the queries that create the different types of rows. It would be nice if we could make the output sort into the order that you presented. For now I will just use some arbitraryly large values for the GROUP, SUBGROUP and ID variables to make that work.
proc sql ;
create table want as
select * from have
union
select group,subgroup,9999 as id,'sub_total' as name,. as age
, sum(sub1) as sub1
, sum(sub2) as sub2
, sum(sub3) as sub3
from have group by group,subgroup
union
select group,9999 as subgroup,9999 as id,'total' as name,. as age
, sum(sub1) as sub1
, sum(sub2) as sub2
, sum(sub3) as sub3
from have group by group
union
select 9999 as group,9999 as subgroup,9999 as id,'grand_total' as name,. as age
, sum(sub1) as sub1
, sum(sub2) as sub2
, sum(sub3) as sub3
from have
;
quit;
... View more
