Within group calculations (without myself) in an unbalanced panel

[Priyoma Mustafi]

Hi Sir,
I need help with the following. I had posted this earlier but did not receive any response.

My data looks like the following :

family ID myear edu
A A1 1981 9
A A2 1984 13
A A3 1985 18
A A4 1987 10
A A5 1990 5

myear is the year of marriage.


I need to create an unbalanced (every ID is repeated from myear till 1990 ) panel data with a rolling sum variable at the ID-year level.


For A2 in 1985, he is exposed to the 9 years education of A1 (not his own). This continues till A3 marries into family in 1985, and it changes to 27 till 1987 when A4 marries. It is 37 for 1987-88. It changes to 42 in 1990 with A5 marrying.

(Similarly for every individual)

The finaldata should look like this: (numerator is the variable I want and described above)

family ID myear year dummy edu numerator denominator Avg exposure to edu by year A A1 81 81 0 9 0 0 . A A1 81 82 1 9 0 0 . A A1 81 83 0 9 0 0 . A A1 81 84 0 9 0 0 . A A1 81 85 0 9 0 0 . A A1 81 86 0 9 0 0 A A1 81 87 1 9 0 0 . A A1 81 88 0 9 0 0 . A A1 81 89 0 9 0 0 . A A1 81 90 0 9 0 0 . A A2 84 84 0 13 9 1 9 A A2 84 85 0 13 27 2 13.5 A A2 84 86 1 13 27 2 13.5 A A2 84 87 0 13 37 3 12.33333333 A A2 84 88 1 13 37 3 12.33333333 A A2 84 89 0 13 37 3 12.33333333 A A2 84 90 0 13 42 4 10.5 A A3 85 85 0 18 22 2 11 A A3 85 86 0 18 22 2 11 A A3 85 87 1 18 32 3 10.66666667 A A3 85 88 0 18 32 3 10.66666667 A A3 85 89 1 18 32 3 10.66666667 A A3 85 90 0 18 37 4 9.25 A A4 87 87 0 10 40 3 13.33333333 A A4 87 88 0 10 40 3 13.33333333 A A4 87 89 0 10 40 3 13.33333333 A A4 87 90 1 10 45 4 11.25 A A5 90 90 1 5 50 4 12.5

Any idea how I can go about this? I have not understood how to tell stata that it is the same household but you take women after my marriage, and make it a sum without including myself.

[Priyoma Mustafi]

Does anyone have any suggestion? A lil guidance would help. I am just stuck.
I have managed to get the data to an ID-year level (by using expand) ie now I have every ID repeated from myear till 1990. (ie If for A1 if she got married in 81, her data runs from 1981 to 1990, and so on for others) But in this I am unable to tell Stata correctly that for A1, in her family everytime a new member is married in any of the later years between 1981 and 1990, the sum gets added by that person's education.

The interpretation being, what was the average education in the household for every individual in every year? Thus whenever someone new marries, the sum of education (which would be the numerator of the avg variable) changes?

I am sorry if this is unclear. But I hope the table which I have attached above is clear enough.

[Daniel Bela]

Hi Priyoma,

you did not receive a quick reply because you did not follow the FAQ (also linked at the top of each page) on how to post questions (especially: the section about posting data examples with -dataex-). Please do so for future posts.

Thus said, I'm not sure I understand what you're up to. Why does the "exposed education" not increase for person A1?

Anyways, here's what I think may be quite close to the desired result, but not identical to what your table presents:

clear
input str1(family) str2(ID) myear edu
"A" "A1" 1981 9
"A" "A2" 1984 13
"A" "A3" 1985 18
"A" "A4" 1987 10
"A" "A5" 1990 5
end

* declare temporary variabels
tempvar maxyear yeardiff

* calculate the maximum year for each family
bysort family : egen `maxyear'=max(myear)

* calculate the individual difference to maxyear
generate `yeardiff'=cond(`maxyear'-myear==0,0,`maxyear'-myear+1)

* expand to one observation per person per year
expand `yeardiff'
bysort family ID (myear) : generate year=myear+_n-1 , before(myear)

* calculate the family-year total for each year
bysort family year : egen yeartotal_family=total(edu)

* substract the individual education from family-year total (is that what you're up to?)
generate yearsum_minus_ego=yeartotal_family-edu

* re-establish original family --> id --> year sort order
sort family ID year

Regards
Bela