Hi there! I have a problem in recoding the panel data.
As you can see below, this panel data describes the interactions of women’s employment history and their fertility. Employ-status is a category variable that shows the status of work &fertility four times. There are 1before 2across 3. after. And for 1before, the amount of variable “bef” was collcted. For 2 across, var “across” was collected. For 3 after, var “af” was collected.


Array

I wanna recode these complicated data into some simple variables. You can see in the table. The rule is:
a. generate variables ”employ_final”&”employ_bef” &”employ_af” &”employ_across”
1. Among the data in four times, if all the valid data are “1before”, then the final var “employ_final” is “1before” and fill in the min of all valid of var “bef” into var “employ_bef” (see pid no.2).
2. If all the valid data are “3after”, then the final var “employ_final” is “3after” and fill in the min of all valid of var “af” into var “employ_af” (see pid no.1).
3. If there is one “2across” in the valid data in four times, the final var “employ_final” is “2across” and fill in the amount of var “across” of the “across” row into var “employ_across” (see pid no.5).
4. In all valid data among four times, if the previous data(_n-1) is "after" and the next data(_n) is"before ", then the var”employ_final” is “4gap”. Meanwhile, the amount of var "af" corresponding to the status “af” row is filled into the “employ_af”. Similarly, the amount of var "bef" corresponding to the status “bef” row is filled into the “employ_bef” (e.g., pid no.4).

b. And I also want to generate a variable called “employ_used” that represents the number of times that previous data was applied (e.g., for first woman, cuz all the status is “after”, and the min of “af” is 10 in forth time. So fill 4 into “employ_used”. And the same as the second&third&fifth women, they should fill 2 1 and 2 separately. And for the forth woman, just write 2 corresponding to “after”line is fine.)

Sorry for the complicated questions....Any help will be appreciated.

Regards,
Catherine