Hello all,

I have dealing with a rather weird problem for the past week and I am hoping you can help. I will try to explain as best as I can. Apologies if this is confusing.

My data is structured as follows:

country year x1 x2 x3 . y1 y2 y3 z1
country1 1990 1 . 8 . 1
country1 1991 1 1 . 7 8 . 1
country1 1992 1 1 . 7 . 9 . 1
country1 1993 . 1 9 1
country1 1994 . 1 1 9 . 3 . 1
country1 1995 1 3 . 2
country1 1996 1 . 5 2
country1 1997 1 5 2
country1 1998 1 5 2
etc
etc
country2 1993 - similarly for country2
country2 1994- similarly for country2
etc
etc

In words, the structure of the data is a panel. The variables x1, x2, x3 are 1s for only some of the observations (missing otherwise). There are three other variables: y1, y2 and y3 (that are defined only when x1, x2 and x3 are 1 respectively). There is also another variable z1 which takes on a sequential numbering (generated using group). What I am trying to calculate is the following variable "whatineed"

country year x1 x2 x3 . y1 y2 y3 z1 whatineed
country1 1990 1 . 8 . 1 . 27
country1 1991 1 1 . 7 8 . 1 . 27
country1 1992 1 1 . 7 . 9 . 1 . 27
country1 1993 . 1 9 1 . 27
country1 1994 . 1 1 9 . 3 . 1 . 27
country1 1995 1 3 . 2 . 8
country1 1996 1 . 5 2. 8
country1 1997 1 5 2. 8
country1 1998 1 5 2. 8
etc
etc
country2 1993
country2 1994
etc
etc

In words, the variable "whatineed" is the sum of 7+8+9+3 when z1 == 1 (because these are the values that occur in variables y1, y2 and y3 when when z1 = 1), and 5+3 = 8 when z1 == 2 (because these are the values that occur in variables y1, y2 and y3 when when z1 = 2).

Similar data exists for country2, etc and so I am looking for a solution that allows the use of "by"

I will appreciate any help. Thanks!