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

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


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/