Calculating Percentages in Total Row

[gibsonsg]

Hi everyone,

I want to transform the Table-1 to the Table-2. In “Proc Tabulate”, I cannot calculate to the percentages in the TOTAL row. Is there any simple solution for that?

Thanks in advance.

Table-1 

ZONE_NAME	VALUE
ZONE1	A
ZONE1	A
ZONE1	B
ZONE1	B
ZONE1	C
ZONE2	B
ZONE2	B
ZONE2	B
ZONE2	C
ZONE2	A
ZONE2	C
ZONE3	C
ZONE3	C
ZONE3	C
ZONE3	B
ZONE3	B
ZONE3	A
ZONE3	A

Table-2

	ZONE1	ZONE2	CHANGE (ZONE2 --> ZONE1)	ZONE3	CHANGE (ZONE3 --> ZONE2)
A	2	1	-50%	2	100%
B	2	3	50%	2	-33%
C	1	2	100%	3	50%
TOTAL	5	6	20%	7	17%

[ballardw]

Tabulate does not do calculations between columns. You can the percent of a count or sum of another variable but not a "direction" such as -50%.

Proc Report will do so though I find the syntax cumbersome and generally create such summaries with either a summary procedure like Proc Means or Summary and possibly a data step. And then use a report procedure like Print, Tabulate or Report to display the result.

[learsaas · Accepted solution]

proc report data=table_1 nowd;
	column VALUE ZONE_NAME pct1 pct2;
	define VALUE /group '';
	define ZONE_NAME /across '';
	define pct1 /computed 'CHANGE (ZONE2 --> ZONE1)' format=percent8.;
	define pct2 /computed 'CHANGE (ZONE2 --> ZONE2)' format=percent8.;
	compute pct1;
		pct1=(_C3_-_C2_)/_C2_;
	endcomp;
	compute pct2;
		pct2=(_C4_-_C3_)/_C3_;
	endcomp;
	rbreak after /summarize;
	compute after;
		VALUE='TOTAL';
	endcomp;
run;

[gibsonsg]

It is a really good solution for me. Thanks so much for your answer, "learsaas".