Proc tabulate - summing a sum

[Bootler]

I created some dummy data

Data crop;
input season $ farm $ food $ amount;
datalines;
Fall'13 Florida apples 23
Spr'14 Florida apples 18
Sumr'14  Florida apples 19
Fall'13 Alabama apples 23
Spr'14 Alabama apples 25
Sumr'14  Alabama apples 24
Fall'13 Georgia apples 12
Spr'14 Georgia apples 14
Sumr'14  Georgia apples 11
Fall'13 Florida oranges 50
Spr'14 Florida oranges 48
Sumr'14  Florida oranges 32
Fall'13 Alabama oranges 10
Spr'14 Alabama oranges 9
Sumr'14  Alabama oranges 8
Fall'13 Georgia oranges 26
Spr'14 Georgia oranges 18
Sumr'14  Georgia oranges 11
;
run;

proc tabulate data=crop;
class season farm food;
var amount;
tables food*amount, farm*(season);
run;

and it made

			farm
			Alabama			Florida			Georgia
			season			season			season
			Fall'13	Spr'14	Sumr'14	Fall'13	Spr'14	Sumr'14	Fall'13	Spr'14	Sumr'14
food
apples	amount	Sum	23	25	24	23	18	19	12	14	11
oranges	amount	Sum	10	9	8	50	48	32	26	18	11

I want to create a new row that sums  each column

			farm
			Alabama			Florida			Georgia
			season			season			season
			Fall'13	Spr'14	Sumr'14	Fall'13	Spr'14	Sumr'14	Fall'13	Spr'14	Sumr'14
food
apples	amount	Sum	23	25	24	23	18	19	12	14	11
oranges	amount	Sum	10	9	8	50	48	32	26	18	11
Total			33	34	36	73	66	41	38	32	22

However if I try using the all statement

proc tabulate data=crop;
class season farm food;
var amount;
tables food*amount all, farm*(season);
run;

it produces

			farm
			Alabama			Florida			Georgia
			season			season			season
			Fall'13	Spr'14	Sumr'14	Fall'13	Spr'14	Sumr'14	Fall'13	Spr'14	Sumr'14
food
apples	amount	Sum	23	25	24	23	18	19	12	14	11
oranges	amount	Sum	10	9	8	50	48	32	26	18	11
All		N	2	2	2	2	2	2	2	2	2

Any help would be appreciated

[PGStats · Accepted solution]

Try instead:

proc tabulate data=crop;
class season farm food;
var amount;
tables (food all)*amount, farm*season;
run;

[Bootler]

Thanks I thought I tried that, but I guess I did not. 🙂

That's what happens when you stare at a problem too long.