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

[Nick Cox <njcoxstata@gmail.com>]

Using the approach indicated this could be

clear

input id      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
end



bysort family (person) : gen Person = _n
su Person, meanonly
local imax = r(N)

gen count = 0
gen work = .

qui forval i = 1/`imax' {
by family: replace work = sum(sex == 1 & ((age[`i'] - age) > 10))
by family: replace count = work[_N] if _n == `i'
}

drop work
list, sepby(family)

The approach is in some ways awkward compared with the -joinby-  and
-collapse- magic shown by Robert Picard.

But it has a certain directness as implementing

initialise count = 0

foreach family member {
          count how many are sex 1 and ten years or more younger
          replace count with result for that family member
}

Note that the loop is over family membership, not observations.
Missing ages should be watched out for.

Nick
njcoxstata@gmail.com


On 13 October 2013 23:57, 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
>
*
*   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/