Summing Over Groups

[Owen Wallbanks]

Dear all

I have a dataset organised in three levels

• Children, nested within...
• Nuclear families, nested within...
• Extended families

I want to construct a variable for the total number of children per extended family unit. For example, the table below shows an extended family {1} containing two nuclear families {1,2} that have 3 and 2 children respectively and I want to construct a variable that gives the value 3+2 = 5.

efamid	nfamid	personid	childnum	extfam
1	1	1	3	5
1	1	2	3	5
1	1	3	3	5
1	2	4	2	5
1	2	5	2	5

Any help would be much appreciated.

Many thanks
Owen

[Tiago Pereira]

sort efamid
by efamid : gen total_members = _N

Does it work?

[William Lisowski]

This may start you in a useful direction. The key is to pick a single observation from each nuclear family to include in the total for the extended family.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(efamid nfamid personid childnum extfam)
1 1 1 3 5
1 1 2 3 5
1 1 3 3 5
1 2 4 2 5
1 2 5 2 5
end

sort efamid nfamid personid

// we are going to assume that for each nuclear family
// every observation has the same value of childnum

egen tototal = tag(efamid nfamid)
by efamid: egen echildnum = total(cond(tototal,childnum,0))

list, clean abbreviate(12)

. list, clean abbreviate(12)

       efamid   nfamid   personid   childnum   extfam   tototal   echildnum  
  1.        1        1          1          3        5         1           5  
  2.        1        1          2          3        5         0           5  
  3.        1        1          3          3        5         0           5  
  4.        1        2          4          2        5         1           5  
  5.        1        2          5          2        5         0           5

[Owen Wallbanks]

Thank you both for your responses.

Tiago: unfortunately there is not an observation for every child within the extended family, otherwise the solution you proposed would work well.

William: the solution you proposed did the trick.

Best Wishes
Owen