topic Re: Proc Tabulate: How do I do average % ? in SAS Procedures

Proc Tabulate: How do I do average % ?

Yennie — Fri, 12 Mar 2010 03:57:40 GMT

Hi there,

I tabulating my results as below.

I would like to create an extra column known as
claim_freq and this column is basically take from the fomula of Total SUM of claimCount / Total SUM of epy

can someone help me out on this one please?

Proc Tabulate Data = SummaryTable;
Class FRUITS;
Var claimCount epy incurToDate earnedPrem;
Table ALL FRUITS,
SUM*claimCount, SUM*epy, MEAN*(claim_freq= SUM of claimCount / SUM of epy ) ;
Run;

Re: Proc Tabulate: How do I do average % ?

yonib — Mon, 15 Mar 2010 07:23:23 GMT

Hi Yennie,
You cant create new vars on proc tabulate procedure,
You can do it with proc report or to manipulate the data before....

Re: Proc Tabulate: How do I do average % ?

ChrisNZ — Thu, 18 Mar 2010 21:35:31 GMT

Like so?

proc tabulate data = sashelp.class;
class SEX;
var WEIGHT HEIGHT ;
table (all SEX) ,HEIGHT*sum WEIGHT*sum WEIGHT*pctsum='Ratio' ;
run;

Re: Proc Tabulate: How do I do average % ?

Yennie — Fri, 19 Mar 2010 01:01:17 GMT

Hi Chris,

thanks heaps on this one! Do you know what happen if it's just for average price $.

whereby say the claims cost per policy is = total amount of money inccured to date/ number of days acrrued ... not in percentage though.. it's dollar6.2.

cheers.

Re: Proc Tabulate: How do I do average % ?

ChrisNZ — Sat, 20 Mar 2010 09:34:39 GMT

You mean the ratio (cost per policy) has to be done at observation level? and then you derive the mean of the ratios?
If so, you must compute the individual ratios before hand, proc tabulate cannot do the record-level calculation.
Having said that, the mean of the ratios seems wrong in this case.
The ratio of the sums is the number you want if I understand your data properly.

Re: Proc Tabulate: How do I do average % ?

Peter_C — Wed, 07 Apr 2010 17:04:06 GMT

ratios in Tabulate?
no problem!
(well .. almost no problem)
Just use pctsum on analysis variable for numerator and point denominator definition to the denominator of the ratio.
After that, you just need to tame the tabulate tendancy to present the result as percentage points with 10% as 10 and not .10, but proc format solves that too!
[pre]Proc format ;
picture mnyR(round) other = '00,000,001.23'( mult=1 prefix='$' ) ;
run;[/pre] * seems it needs that MULT=1 !;[pre]Proc Tabulate Data= SummaryTable ;
Class FRUITS ;
Var claimCount epy incurToDate earnedPrem ;
Table ALL FRUITS
, SUM=' '*claimCount SUM=' '*epy
claimCount=' '*pctsum='claim freq' *f= mnyR. ;
Run ; [/pre]
not quite sure why it is claimCount/epy, but hope the description above will clarify alternative ratios.

PeterC forgot to add the $
Message was edited by: Peter.C