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
Try instead:
proc tabulate data=crop;
class season farm food;
var amount;
tables (food all)*amount, farm*season;
run;
Try instead:
proc tabulate data=crop;
class season farm food;
var amount;
tables (food all)*amount, farm*season;
run;
Thanks I thought I tried that, but I guess I did not. 🙂
That's what happens when you stare at a problem too long.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.