Generate a new variable conditional on other variable value on different rows

[Ludvig Olsson]

Cfar1	ku1	ku2	Year	cfar2
1	22	22	2005	1
2	33	56	2006	3
3	56	33	2007	2
4	.	89	2008	.
5	91	.	2009	.

Hello!

I want to generate a new variable (cfar2) where it takes the value of cfar1 when ku2==ku1, even when they are not on the same row. For example, when ku2==22 then cfar2==1 (see row 1 and color green) Another example is when ku2==56 then cfar2==3 (see color red). Also, whenever ku1==. or ku2==. then cfar2==.

Years are included in the dataset but it's not relevant for the creation of cfar2.

I would have no issues with this if ku1 and ku2 had the same values on the same row (like in row 1), but that's not the case.

Unfortunately I am not able to share the data with you so I constructed the table above for illustration.

Thanks!

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(cfar1 ku1 ku2) int year byte cfar2
1 22 22 2005 1
2 33 56 2006 3
3 56 33 2007 2
4  . 89 2008 .
5 91  . 2009 .
end

frame put cfar1 ku1 if !missing(ku1), into(match)
frlink m:1 ku2, frame(match ku1)
frget wanted=cfar1, from(match)
drop match
frame drop match

Res.:

. l

     +-------------------------------------------+
     | cfar1   ku1   ku2   year   cfar2   wanted |
     |-------------------------------------------|
  1. |     1    22    22   2005       1        1 |
  2. |     2    33    56   2006       3        3 |
  3. |     3    56    33   2007       2        2 |
  4. |     4     .    89   2008       .        . |
  5. |     5    91     .   2009       .        . |
     +-------------------------------------------+

.