Hello
I am using proc tabulate to create a 2 dimensional summary report.
There are 4 class vars and 2 analysis vars.
I want to have a Total row for each Z1 level so in the output I want to get 2 total rows (because there are 2 levels for Z1).
The rows in the output will be:
a Carrot Q
a Flowers T
a Orange S
Total (It means Total of a)
b Carrot Q
b Flowers T
b Orange S
Total (It means total of b)
I added "ALL" keyword after Z1 but the result was different than I wanted.
What is the way to solve it please?
Thank you so much
Jo
Data Rawtbl;
input ID Z1 $ Z2 $ Z3 $ Z4 Y1 Y2;
cards;
1 a Flowers T 2019 100 1
2 b Flowers T 2019 90 1
3 a Flowers T 2018 70 1
4 a Orange S 2019 40 0
5 a Orange S 2018 30 0
6 b Orange S 2018 120 0
7 b Orange S 2018 75 0
8 b Carrot Q 2019 150 0
9 a Carrot Q 2019 90 0
10 b Carrot Q 2018 50 0
;
run;
proc tabulate data=Rawtbl format=comma8. missing;
class z1 z2 z3 z4;
var Y1 Y2;
table (Z1='' ALL) *(Z2='')*(Z3=''), (Z4='')*(Y1='')*(N='customers' sum='Total Y1') (Y2=''*max='max Y2')/box='Z1/Z2/Z3';
Run;
Hi @Ronein
Try this:
proc tabulate data=Rawtbl format=comma8. missing;
class z1 z2 z3 z4;
var Y1 Y2;
table (Z1='')*((Z2='')*(Z3='') (ALL)),
(Z4='')*(Y1='')*(N='customers' sum='Total Y1') (Y2=''*max='max Y2')/ box='Z1/Z2/Z3';
run;
Output:
Best,
> Total row for each Z1 level
If you want a total for each Z1, you want to sum the Z2s.
table (Z1='') *(Z2='' ALL)*(Z3=''), (Z4='')*(Y1='')*(N='customers' sum='Total Y1') (Y2=''*max='max Y2')/box='Z1/Z2/Z3';
Please count how many rows you got in the output.
IT is not what I wanted.
I want to get 8 rows
a Carrot Q
a Flowers T
a Orange S
Total a
b carrot Q
b Flowers T
b Orange S
Total b
Hi @Ronein
Try this:
proc tabulate data=Rawtbl format=comma8. missing;
class z1 z2 z3 z4;
var Y1 Y2;
table (Z1='')*((Z2='')*(Z3='') (ALL)),
(Z4='')*(Y1='')*(N='customers' sum='Total Y1') (Y2=''*max='max Y2')/ box='Z1/Z2/Z3';
run;
Output:
Best,
Very nice solution!!!
Thank you so much!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.