## Help on Proc Tabulate to get the Total value by Percentage.

Occasional Contributor
Posts: 12

# Help on Proc Tabulate to get the Total value by Percentage.

Hi SAS Gurus,

This would be my 2nd thread here. I'll waste no time here. My situation is as follows:

Basically I have already done a proc tabulate to tabulate the values for var A together with var B, in 12 period (can be considered 12 months) and then total up the values.

proc tabulate data = abc;
class period;
var a b;
table a*f=comma9.1 b*f=percentn9.2, period=' '*SUM=' ' ALL='Total'*SUM=' ';
run;

The result is as below:
Period 1 2 3 4 5......... 12 Total
A 12 42 32 11 43........ 71 425
B 21.1% 38.2% 12.5% 3.0% 11.1%....41.1% 230.2%

My problem is, whatever i do or try to change, the total will always add up especially for var B, which is not correct as the correct value should be the calculation between 2 other variables (which are A/x) that are used to calculate percentage of B.

Is there any anything I can do to correct the total % of B so that it will relect the correct % value (total A divided by total x) using proc tabulate?

Thanks for your time.
Occasional Contributor
Posts: 11

## Re: Help on Proc Tabulate to get the Total value by Percentage.

In this case, I believe the "ALL" statement will not give you what you need. You could bring the x variable into a proc report and use a compute block. Or you could calculate the totals before displaying the data with proc tabulate. Here's a sample way to the second approach.

proc format;
value \$period_group (multilabel)
'01'='01'
'02'='02'
'03'='03'
'04'='04'
'05'='05'
'06'='06'
'07'='07'
'08'='08'
'09'='09'
'10'='10'
'11'='11'
'12'='12'
'01'-'12'='Total';
run;

**** Create Sample Data *****;
data sample_data;
do i=1 to 12;
do j=1 to 20;
month=i;
a= int(100*rand('uniform'));
x= int(100*rand('uniform'));

*** make the denominator bigger than the numerator ***;
if x > a then output;
end;
end;

keep month a x;
run;

proc sort data=sample_data;
by month;
run;

***** Limit sample data to one row per period *****;
data limit_data;
length period \$2;
set sample_data;
by month;

if first.month;

*** create period variable ***;
if 1 le month le 9 then period='0'||left(month);
else if 10-12 then period=month;

run;

proc summary data=limit_data;
class period /mlf;
format period \$period_group.;
var a x;
types period ;
output out=group_periods (drop=_type_ _freq_)sum=;
run;

data abc;
set group_periods;

*** b calculation ***;
b=(a/x);

run;

proc tabulate data=abc;
class period ;
format period ;
var a b;
table a*f=comma9.0 b*f=percent9.2, period=' '*sum=' ' ;
keylabel sum=' ';

run;
Occasional Contributor
Posts: 12

## Re: Help on Proc Tabulate to get the Total value by Percentage.

Hi Andy.
Thank you for your response. I am now trying to apply your logic into my program. Nice data generation there I must say.

Will let you know if there's any further issue regarding this.
Discussion stats
• 2 replies
• 163 views
• 0 likes
• 2 in conversation