turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Help on Proc Tabulate to get the Total value by Pe...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2011 05:55 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2011 01:33 PM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AndyJ

03-01-2011 03:29 AM

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.

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.