Re: st: Create a new variable based on the current observation and values of other observations in the same group

[Robert Picard <picard@netbox.com>]

A simple way of doing this without a loop is to use -joinby- to form
all pairwise combination of persons within a family. It's then easy to
identify cases that meet the desired condition and use -collapse- to
reduce to the original observations while counting the number of cases
that meet the condition.

Robert

* ------------------ begin example --------------
clear
input family person sex age
 1 1 1 36
 1 2 1 16
 1 3 1 14
 2 1 0 45
 2 2 1 42
 2 3 0 14
 2 4 1 12
 2 5 0 10
 3 1 0 39
 3 2 1 36
 3 3 0 11
 3 4 1 9
 3 5 1 7
 3 6 1 3
 end
tempfile main
save "`main'"

* for each person in each family, form all pairwise
* combinations with other persons in the family
rename (person sex age) =0
joinby family using "`main'"

* identify cases that must be counted
gen ncount = (sex == 1) & (age0 - age >= 10)

* restore original obs while adding ncount cases
collapse sex0 age0 (sum) ncount, by(family person0)
list, noobs sepby(family)

* ------------------ end example ----------------

On Sun, Oct 13, 2013 at 6:57 PM, Huanhuan Shi <huanhuanshi09@gmail.com> wrote:
> Dear Statalist,
>
> I have a problem which is similar to but different from this post.
> http://www.stata.com/support/faqs/data-management/creating-variables-recording-properties/
>
> I tried to modify the code suggested by Nicholas J. Cox but can not
> figure it out. Hope I can get help from you.
> The problem I am facing is as follows:
>
> First, the data structure is shown below. I have family groups and
> individuals in each family group.
>
>         family    person   sex      age
>   1.         1          1          1         36
>   2.         1          2          1         16
>   3.         1          3          1         14
>
>   4.         2          1          0         45
>   5.         2          2          1         42
>   6.         2          3          0         14
>   7.         2          4          1         12
>   8.         2          5          0         10
>
>   9.         3          1          0         39
> 10.         3          2          1         36
> 11.         3          3          0         11
> 12.         3          4          1          9
> 13.         3          5          1          7
> 14.         3          6          1          3
>
>
> What I want to do is to create a new variable "count" that count the
> number of individual that sex is 1  and10 years younger than the
> person within the same family. So the result should be like:
>
>
>         family    person   sex      age      count
>   1.         1          1          1         36           2
>   2.         1          2          1         16           0
>   3.         1          3          1         14           0
>
>   4.         2          1          0         45           1
>   5.         2          2          1         42           1
>   6.         2          3          0         14           0
>   7.         2          4          1         12           0
>   8.         2          5          0         10           0
>
>   9.         3          1          0         39           3
> 10.         3          2          1         36           3
> 11.         3          3          0         11           0
> 12.         3          4          1          9            0
> 13.         3          5          1          7            0
> 14.         3          6          1          3            0
>
>
> Thank you so much!!
>
> Huanhuan
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/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/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/