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
- /
- weighted average with negative weights

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-02-2014 07:59 AM

Hi,

Is the only way to derive a weighted average when some weights are negative to use proc sql or similar?

Can't proc means do it? I find this hard to believe.

Thank you.

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

Posted in reply to ChrisNZ

06-02-2014 08:45 AM

Let me ask the dumb question first. What is the meaning of a negative weight in this context? Is it the same as a positive weight for a negative value?

Steve Denham

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

Posted in reply to SteveDenham

06-02-2014 09:09 AM

Indeed it is. Haha I see where you are going...

I could indeed create a second set of variables where the negativity is transferred to the analysis variable.

That's messy (I have many such calculations, and I also need to derive the sum of the weights), but better than doing all the crossings in sql I suppose.

Thanks for the idea.

Still, I can't believe proc means can't do it. It would be so easy, it's mad it's been omitted.

Message was edited by: Christian Graffeuille Mmm... Actually no, the denominator will be too large if I sum the absolute values of the weights. OK, awaiting the next post...

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

Posted in reply to ChrisNZ

06-02-2014 10:43 AM

Since I am using several proc means options which will be a huge pain to replicate in proc sql (completetypes,

preloadfmt, and especially mlf), what I'll do for now is:

1) derive the products before doing the summaries,

2) only calculate sums in my numerous proc means,

3) derive ratios.

I am not impressed that negative weights have been barred.

An option would have been so very easy to implement (just leave the data as is), and its absence is such a pain to circumvent.

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

Posted in reply to ChrisNZ

06-02-2014 10:46 AM

That ought to solve things.

I am still curious. What is the meaning of a negative weight, if you can share the context?

Steve Denham

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

Posted in reply to SteveDenham

06-02-2014 11:33 AM

I am unsure how much I am allowed to divulge, but for DQ reasons some group totals are misestimated and compensation values are inserted to make the overall totals align. These compensation values can be negative.

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

Posted in reply to ChrisNZ

06-02-2014 12:22 PM

Given that, your approach to caluclate sums makes even more sense to me.

Steve Denham

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

Posted in reply to SteveDenham

06-02-2014 01:24 PM

The approach I had to use is needlessly complex though.

proc means is meant to do just this sort of things, and I had instead to fudge a workaround that will be much more difficult to maintain.

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

Posted in reply to ChrisNZ

06-02-2014 02:07 PM

Mathematically, a weighted average is only defined for positive weights. The denominator of a weighted mean is the sum of the weights. See the Wikipedia article: Weighted arithmetic mean - Wikipedia, the free encyclopedia

If you attempt to introduce negative weights, all kinds of bad things can happen, such as the denominator being zero. If the weights are w1=+1/2 and w2=-1/2, what is the weighted average of x1=1 and x2=2? Is it the same or different from the weighted average of x1=1 and x2=200?

Of course PROC MEANS computes much more than just means. Computing a weighted variance or quantile are not defnied for negative weights. Do you accept a negative variance? I don't.

I think in most cases negative weights do not make sense. If you can cite a published paper that defines weighted descriptive statistics for negative weights, I would like to read it.

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

Posted in reply to Rick_SAS

06-03-2014 03:52 AM

And then there is the real world, Rick.

I agree that negative weights are statistically meaningless.

Just like in my case, negative exposures, or in other cases negative inventories or distances are meaningless.

There is no entry for negative such measures on wikipedia either.

Yet sometimes these calculations have to be made.

If the world, and the world's data, were perfect, you'd be right.

As it is, this calculation is being performed, because it is necessary, and it would be very nice if proc means was unbridled and did for us what it does so well within its present perfect-world statistically-unspoiled boundaries.

We could call the option to enable this feature ngtwgt-real-statisticians-will-never-touch-this if needed, but it would nevertheless be useful in some (possibly rare) cases, and the cost of adding it is very low since it only involves leaving the data is and checking for nil denominators, which I don't doubt is already being considered.

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

Posted in reply to ChrisNZ

06-03-2014 09:19 AM

if you don't want SQL, why not use data step ?

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

Posted in reply to ChrisNZ

10-01-2014 01:16 PM

Maybe transform your fields so there aren't any negative values?