topic Re: proc tabulate with 4 class vars and problem to get total in SAS Programming

proc tabulate with 4 class vars and problem to get total

Ronein — Tue, 05 May 2020 05:34:29 GMT

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

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;

Re: proc tabulate with 4 class vars and problem to get total

ChrisNZ — Tue, 05 May 2020 05:51:26 GMT

> 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';

Re: proc tabulate with 4 class vars and problem to get total

Ronein — Tue, 05 May 2020 06:05:50 GMT

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

Re: proc tabulate with 4 class vars and problem to get total

ed_sas_member — Tue, 05 May 2020 08:05:07 GMT

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,

Re: proc tabulate with 4 class vars and problem to get total

Ronein — Tue, 05 May 2020 09:06:37 GMT

Very nice solution!!!

Thank you so much!

Re: proc tabulate with 4 class vars and problem to get total

ed_sas_member — Tue, 05 May 2020 09:08:34 GMT

Thank you @Ronein
Have a lovely day