Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

# RE: st: Calculate weighted average across variables with externally given weights - controlling for missing values

 From Nick Cox To "'statalist@hsphsun2.harvard.edu'" Subject RE: st: Calculate weighted average across variables with externally given weights - controlling for missing values Date Mon, 3 Oct 2011 16:58:42 +0100

```Good resolution. Thanks for closure. See also

SJ-10-2 dm0049  . . . . . . . . . . . . . Stata tip 86: The missing() function
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  B. Rising
Q2/10   SJ 10(2):303--304                                (no commands)
tip on using the missing() function to deal with missing
values

on how useful -missing()- can be.

Nick
n.j.cox@durham.ac.uk

Andreas Karpf

thanks nick,

i have solved my problem. i wasn't aware that i could combine two
variables in cond(missing(x, weight), 0, weight) after your first
mail. thank you very much!

best regards,

andreas

2011/10/3 Nick Cox <n.j.cox@durham.ac.uk>:

> I don't understand the difficulty. Multiplying by zero is the same as omitting. Therefore, whatever you want to be omitted should be zero, or be multiplied by zero. The effect is the same. At most you might need an expression for your weights which is something like
>
> cond(missing(x, weight), 0, weight)
>
> I am not aware of a canned command for this purpose and would have mentioned it if I were. In principle you just need to
>
> gen total = 0
>
> foreach <arguments in list of arguments> {
>   replace total = total + <weight> * <variable>
> }
>
> -egen, rowtotal()- is a convenience command for row totals but it doesn't allow weights.
>
> Nick
> n.j.cox@durham.ac.uk
>
>
> -----Original Message-----
> From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Andreas Karpf
> Sent: 03 October 2011 10:17
> To: statalist@hsphsun2.harvard.edu
> Subject: Re: st: Calculate weighted average across variables with externally given weights - controlling for missing values
>
. I thought a while about the solution you gave me and I
> don't really see how I can handle my problem with that. Setting the
> missing values to zero is quite obvious and was also my first idea.
> This solves the problem in the enumerator but unfortunately not in the
> denominator. If one value is missing I don't want to divide by the
> whole sume of weights (see problem 2)). A solution via dummy variables
> seems quite cumbersome and I don't really know how I would have to do
> it because I am having time series data and the weights apply for
> different variables and not for different values of one variable.
> isn't there any stata command for exactly this purpose (cross-variable
> weighted averages with user supplied weights) which I could combine
> with cond(missing(x), 0, x).
>
> Thank you very much for your help,
>
> Andreas
>
> 2011/10/3 Nick Cox <njcoxstata@gmail.com>:
>> To get Stata to treat values of -x- as 0 if they are missing you can
>>
>> cond(missing(x), 0, x)
>>
>> or use -mvdecode-.
>>
>> Nick
>>
>> On Mon, Oct 3, 2011 at 2:16 AM, Andreas Karpf <andreas.karpf@gmail.com> wrote:
>>
>>> I have a couple of time series variables for different industrial
>>> sectors like manufacturing, services industry, communication industry
>>> etc.
>>>
>>> t  ; var_sect_1 ; var_sect_2 ; var_sect_3 ; var_sect_4;
>>> jan ; ; ; ; ;
>>> feb ; ; ; ; ;
>>> mar ; ; ; ; ;
>>> apr ; ; ; ; ;
>>>
>>> What I want to do (example january):
>>>
>>> weight_avg_january= var_sect_1[jan] *weight_sect_1 +
>>> var_sect_2[jan]*weight_sect_2 + var_sect_3[jan]*weight_sect_3 +
>>> var_sect_4[jan]*weight_sect_4/(weight_sect_1+weight_sect_2+weight_sect_3+weight_sect_4)
>>>
>>> if there is however a missing value for january sector 1 it should look like:
>>>
>>> weight_avg_january= var_sect_2[jan]*weight_sect_2 +
>>> var_sect_3[jan]*weight_sect_3 +
>>> var_sect_4[jan]*weight_sect_4/(weight_sect_2+weight_sect_3+weight_sect_4)
>>>
>>> these data relates to a kind of business monitor survey and i would
>>> like to calculate the aggregate indicator by using sectorial weights,
>>> this means weights
>>> which correspond to the contribution of each sector (services,
>>> manufacturing) to the gdp. i at first though i could do that by hand
>>> but than i realized that 1) if there is
>>> one missing value in e.g. sector 1 in january stata outputs a missing
>>> value for the weighted average for january. so it doesn't just ignore
>>> the mv but it refuses to calculate the datapoints which are there. 2)
>>> even if problem number one would be solved of course the denominator
>>> would not be correct because if the sector 1 data in january is
>>> missing also the weight in the denominator for sector 1 should be
>>> omitted.
>>> The weights i am referring to are from an external statistic.

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```