topic Re: (sort of) weighted average in SAS Procedures

(sort of) weighted average

EH — Fri, 08 Jan 2016 11:48:57 GMT

Hello,

Can anyone tell me how to calculate weighted averages in this example:

data premium;
   input id fraction premium;
   datalines;
1 1 1000
2 0.5 500
run;

Customers 1 and 2 both payed the same premium, but no. 2 was insured for only six months.
They the same annual premium, so in my opinion the average premium per person is 1000.
However, using weight does not work correctly in this case:

proc means mean;
weight fraction;
var premium;
run;

It returns 833 as the average premium.
Currently I solve this by exporting means output, summing fraction and premium and divide.

Does anyone know of an easy, better way - without calculating new variables in the datastep (there are many variables)?

Thanks,
Eric

Re: (sort of) weighted average

FreelanceReinh — Fri, 08 Jan 2016 12:17:27 GMT

You could use PROC SQL:

proc sql;
select mean(premium/fraction) as mean_premium
from premium;
quit;

Re: (sort of) weighted average

ChrisHemedinger — Fri, 08 Jan 2016 13:09:16 GMT

Thanks for asking your question on the communities here. I see you've also asked it in other places (SAS-L, blog comments) so I want to add some more of that context.

It seems like you really want an "annualized premium" -- what each customer is paying per annum (year) as a rate. If the term is something different that 1.0 years, you need a formula to calculate what the annual rate is.

I've seen this answer on SAS-L by "retired mainframer":

data null;
   set premium end=eof;
   do until (eof);
      sum+(premium/fraction);
      count+1;
   end;
   average = sum/count;
   put average=;
run;

The SQL approach by @FreelanceReinh might also serve your purpose.

Re: (sort of) weighted average

EH — Fri, 08 Jan 2016 13:15:05 GMT

Thanks Chris and Reinhard,

The next step is that I want the premiumpaid by No 2 to weigh less than the premium paid by No 1.

If I change the case to

Id 1, fraction=1, premium=900

Id 2, fraction=0.5, premium=600

The average annual premium should be (900 + 600) / 1.5 = 1000.

In Reinhard's case it is (900 + 1200) / 2 = 1050.

I can see it can be solved using Chris' script. But then I have dozens of variables I want to treat this way...

But thanks!! for your quick replies.

Cheers,

Eric

Re: (sort of) weighted average

ChrisHemedinger — Fri, 08 Jan 2016 13:35:33 GMT

If you prefer SQL, I think this accomplishes something similar:

PROC SQL;
   CREATE TABLE WORK.Avgs AS 
   SELECT   (COUNT(t1.id)) AS N, 
            (SUM(t1.fraction)) AS AnnumTotal, 
            (SUM(t1.premium)) AS PremiumTotal, 
          /* AnnualizedAvg */
            calculated PremiumTotal / calculated AnnumTotal AS AnnualizedAvg
      FROM WORK.PREMIUM t1;
QUIT;

Re: (sort of) weighted average

FreelanceReinh — Fri, 08 Jan 2016 13:40:19 GMT

Chris was faster and is correct.

What you want is the weighted mean of the annualized premiums with the fractions being the weights. This, indeed, amounts to (sum of premiums)/(sum of fractions) and hence can be calculated as shown by Chris or similarly as follows:

proc sql;
select sum(premium)/sum(fraction) as mean_premium
from premium;
quit;

Re: (sort of) weighted average

EH — Fri, 08 Jan 2016 14:07:45 GMT

thx!

Re: (sort of) weighted average

EH — Fri, 08 Jan 2016 14:08:15 GMT

Thanks Chris and Reinhard. I feel a macro coming up...

Re: (sort of) weighted average

FreelanceReinh — Fri, 08 Jan 2016 14:10:12 GMT

No, wait a minute! You don't need a macro. I can prepare an array solution for you.

Re: (sort of) weighted average

FreelanceReinh — Fri, 08 Jan 2016 14:23:03 GMT

So, you have more variables like PREMIUM, let's say three, for example: PREMIUM, VAR2, VAR3. And let's assume for a moment that the weights ("fractions") are the same for all these variables. (Or do you have individual fractions for each of them?)

In this case you could extend the DOW loop approach cited by Chris. But I'm afraid there is a correction necessary: The SET statement must be moved inside the DO-UNTIL loop (that's the trick of the DOW loop after all), otherwise we have an infinite loop.

Now you can calculate the target variables, let's call them MEAN_PREMIUM, MEAN_VAR2 and MEAN_VAR3, as follows:

data premium1;
input id fraction premium var2 var3;
cards;
1 1   1000 900 500
2 0.5 500  600 400
;

%let n=3; /* number of variables like PREMIUM */

data _null_;
array p[&n] premium var2 var3;
array s[&n];
array avg[&n] mean_premium mean_var2 mean_var3;
do until (eof);
  set premium1 end=eof;
  do i=1 to &n;
    s[i]+p[i];
  end;
  wgt+fraction;
end;
do i=1 to &n;
  avg[i] = s[i]/wgt;
end;
put avg(*)=;
run;

If you need individual fractions for each of the three variables, we could introduce a fourth array, which would be no problem at all.

Re: (sort of) weighted average

EH — Fri, 08 Jan 2016 14:41:55 GMT

Reinhard, Thanks for your extensive reply. It is definitely something to apply in my case!

Re: (sort of) weighted average

Rick_SAS — Fri, 08 Jan 2016 14:57:21 GMT

Perhaps I am not understanding the problem, but I have to disagree with several of the submitted "solutions." The solution that you accepted as correct does not seem correct to me. Both @ChrisHemedinger and @FreelanceReinh suggested implementations of the formula sum(premium)/sum(fraction). That is not the same as mean(premium/fraction), which I think is the correct formula.

Re: (sort of) weighted average

EH — Fri, 08 Jan 2016 15:13:26 GMT

Hello (again) Rick,

In my data I have premiums and fractions of a year that a customer was insured.

So in my opinion the average premium per customer is sum(premium)/sum(fraction). This is a sort of weighted average, unfortunately not supported by the MEANS procedure.

So being the customer (who is always right ), I think the solutions are correct.

Thanks for your thoughts!

Cheers, Eric

Re: (sort of) weighted average

FreelanceReinh — Fri, 08 Jan 2016 15:16:03 GMT

@Rick_SAS: Well, mean(premium/fraction) was my first suggestion, but then @EH replied "I want the premium paid by No 2 to weigh less than the premium paid by No 1" and presented an example calculation that led us to the sum(premium)/sum(fraction) solution.

Re: (sort of) weighted average

EH — Fri, 08 Jan 2016 15:21:14 GMT

There is a similar question, marked as solved, but I cannot find the solution.

https://communities.sas.com/t5/SAS-Procedures/Weighted-average/m-p/24378#U24378

In the solution is referred to the VARDEF option, but that does not apply to the weighted mean calculation.

Re: (sort of) weighted average

ChrisHemedinger — Fri, 08 Jan 2016 15:25:20 GMT

If VARDEF is what you're looking for, here's the example from SAS doc: https://support.sas.com/documentation/cdl/en/proc/68954/HTML/default/viewer.htm#n1xkqt7u5ylr2kn11174pq11od76.htm#n1934j4pmu3328n1hcczxemu170p

You will need to read through it to see if you can apply that to your situation.

Re: (sort of) weighted average

Rick_SAS — Fri, 08 Jan 2016 16:22:42 GMT

Fine with me. You can use any formula you want.

One reason you won't find this in a SAS procedure is that it is not a mean (weighted or otherwise). In terms of terminology, I would call the formula sum(x)/sum(y) a scaled sum. Notice that if you reorder the weight values, you will get the same scaled sum. The "fraction" values are not attached to the "premium" values in any way, so you'll get the same answer for

1 0.5 1000
2 1    500

and for

1 -99   1000
2 100.5  500

Good luck with your analysis.

Re: (sort of) weighted average

twildone — Sun, 10 Jan 2016 03:10:14 GMT

Hi Eric,

Using Sum(premium)/Sum(Fraction) will not give you the right answer if you are wanting to calculated a weighted average where the weights are the fraction of the year that a premium was paid. From your original example, the Sum(Premium) = 900 + 600 =1500 and Sum(Fraction) = 1.0 + 0.5 = 1.5. As a result, 1500/1.5 = 1000. How can the average of the two premiums be more than either one of them ( 900 or 600). This makes no sense.

If you sum up the product of the premium and fraction and then divide by the sum of the fraction that will and should give you a weighted average for the two premiums ((1.0)(900) +(0.5)(600))/(1.0 + 0.5) = 1200/ 1.5 = 800.

proc sql;
select sum(premium*fraction)/sum(fraction) as mean_premium
from premium;
quit;;

Re: (sort of) weighted average

EH — Mon, 11 Jan 2016 08:21:41 GMT

Hello Twildone,
You are right about the "makes no sense" part. This is "a sort of weighted average", it is the average yearly premium. If you multiply that by the number of weights (fractions) you get the total received premium.
In proc means, the weighted average is calculated as sum(WiXi)/sum(Wi), where my "sort of" requires sum(Xi)/sum(Wi). I am afraid I have to calculate this outside proc means, as suggested by others. Thanks anyway for your thoughts!

Re: (sort of) weighted average

EH — Mon, 11 Jan 2016 08:24:56 GMT

And thanks to this simple question, since friday I received five (5) badges. I'm so happy 🙂