BookmarkSubscribeRSS Feed
KYW
Calcite | Level 5 KYW
Calcite | Level 5
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.
2 REPLIES 2
AndyJ
Fluorite | Level 6
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;
KYW
Calcite | Level 5 KYW
Calcite | Level 5
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 662 views
  • 0 likes
  • 2 in conversation