topic Re: Help on Proc Tabulate to get the Total value by Percentage. in SAS Procedures

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

KYW — Mon, 28 Feb 2011 10:55:28 GMT

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.

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

AndyJ — Mon, 28 Feb 2011 18:33:45 GMT

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;

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

KYW — Tue, 01 Mar 2011 08:29:17 GMT

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.