Dear All, Suppose that I have this data
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id year) str4(x1 x2 x3)
1 2000 "A" "B" "C" 
1 2001 "C" "A" "D"
1 2002 "E" "C" "F"
2 2000 "F" "G" "I"
2 2001 "I" "K" "L"
2 2002 "L" "K" "I"
end
For each `id' and each `year', I'd like to construct a new variable, say `wanted'. Taking id=1, year=2001 as an example, x1=C, x2=A, and x3=D, where C (x3) and A (x1) appears in the previous year as x3 and x1, respectively. Thus, wanted = 2. Similarly, wanted = 1 when id =1 and year = 2002. Any suggestions are highly appreciated.