Re: st: Weighted summing in a loop for members of a group

[Timm Klare <timm.klare@gmail.com>]

Thank you Fernando and Robert, you really helped me, problem solved!

@Fernando: Transforming the sum was quite a nice hint, for the
specified example I could avoid any loop this way. I just created the
sum(Yi) and sum(Xi*Yi) variables and substracted each [_n] observation
in a by command.

@Robert: This is exactly the loop I couldn't come up with, thank you.
It works beyond my specific example (which could be solved after a
transformation using a by routine) and is of great use for me.

Cheers
Timm

2011/3/23 Robert Picard <picard@netbox.com>:
> If I understand correctly the problem, here's one approach:
>
> /*-------------------------- begin example -------------*/
>
> * Setup fake data; start with 3 parent groups
>
> clear
> set obs 3
> gen gp = _n
>
> * Each parent group has a varying number of companies
>
> gen nid = 2 + _n
> expand nid
> sort gp nid
> replace nid = _n
>
> * Create data years
>
> expand 3
> sort gp nid
> by gp nid: gen year = 2000 + _n
>
> * Random total assets, group total assets, and tax rates.
>
> set seed 12345
> gen ta = 100 * runiform()
> sort gp year
> by gp year: egen gta = sum(ta)
> gen tr = 30 * runiform()
>
> * Find how many companies in the largest group
>
> sort gp year nid
> by gp year: gen n = _n
> sum n, meanonly
> local nloop = r(max)
>
> * calculate measure for each obs in the group
>
> gen tada = .
> gen xsum = .
> forvalues i = 1/`nloop' {
>        by gp year: replace xsum = sum((tr[`i']-tr)*ta/gta)
>        by gp year: replace tada = x[_N] if _n == `i'
> }
> /*-------------------------- end example ---------------*/
>
>
2011/3/23 Fernando Rios Avila <f.rios.a@gmail.com>:
> Why dont you try the following:
>
> (X1 - X2)*Y2/Z + (X1 - X3)*Y3/Z + .... + (X1 - X50)*Y50/Z
>
> [(X1 - X2)*Y2 + (X1 - X3)*Y3 + .... + (X1 - X50)*Y50]/Z
>
> [X1*sum(Yi)-sum(Xi*Yi)]/Z
>
> each part individually can be calculated using the egen by group that u are
> already aware of, and the rest is just creating additional variables.
> Fernando

> On Wed, Mar 23, 2011 at 2:31 PM, Timm Klare <timm.klare@gmail.com> wrote:
>> Thank you for your answers. I know the FAQs about members of a group,
>> the -by-, -total- and -egen- functions but my problem lies in the
>> calculation in the loop over a range of values in each group. If there
>> are e.g. 50 group members in one of the groups, the first sum has to
>> be calculated as follows (example variables are X, Y & Z):
>>
>> (X1 - X2)*Y2/Z + (X1 - X3)*Y3/Z + .... + (X1 - X50)*Y50/Z
>>
>> and the 10th sum:
>>
>> (X10 - X1)*Y1/Z + (X10 - X2)*Y2/Z + .... + (X10-X50)*Y50/Z
>>
>> That's why I asked for a capital-sigma like function.
>>
>> Thank you for your consideration
>>
>> 2011/3/23 Nick Cox <njcoxstata@gmail.com>
>>>
>>> There is a -sum()- function and an -egen- function -total()-. But the
>>> twist for you appears to be the calculation w.r.t. all others in a
>>> group. That is an FAQ:
>>>
>>> FAQ     . . Creating variables recording prop. of the other members of a group
>>>        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
>>>        4/05    How do I create variables summarizing for each
>>>                individual properties of the other members of a
>>>                group?
>>>                http://www.stata.com/support/faqs/data/members.html
>>>
>>> Nick
>>>
>>> On Wed, Mar 23, 2011 at 5:15 PM, Timm Klare <timm.klare@gmail.com> wrote:
>>>
>>> > I would appreciate your help in solving the following problem. I work
>>> > on a dataset containing balance sheet and other information about a
>>> > couple of million European companies (AMADEUS).  The data relevant to
>>> > my current problem is made up of the following variables:
>>> >
>>> > GROUP_PARENT    NUM_ID    YEAR    TOTAL_ASSETS    GROUP_TOTAL_ASSETS    TAXRATE
>>> >
>>> > NUM_ID is the unique ID of each company and GROUP_PARENT is the ID of
>>> > the parent company for each group of companies. For each NUM_ID and
>>> > YEAR I need to calculate an new variable. It should sum the
>>> > asset-weighted tax rate differences between each NUM_ID YEAR and all
>>> > other companies in each group:
>>> >
>>> > (sigma sign) (TAXRATE[NUM_ID] – TAXRATE[i])*(TOTAL_ASSETS[i]/GROUP_TOTAL_ASSETS)
>>> >
>>> > Where i corresponds to all other companies in the observed group.
>>> > How can this be done? Is there a “sigma sign” like summing function in
>>> > Stata? I tried using a foreach loop but failed.
>>>
>>> *
>>> *   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/
>>
>> *
>> *   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/
>>
>
> *
> *   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/
>

*
*   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/