CODEWITHSUNDEEP
statisticsstata
RoyalTS
RoyalTS

Reputation: 10203

merge with self in Stata

I have data on two groups, A and B, that looks like this:

group  key   val
A      0.00  1.23
A      0.25  2.31
A      0.50  3.10
A      0.75  4.21
A      1.00  2.51
A      0.00  1.43
B      0.25  1.31
B      0.50  5.10
B      0.75  2.21
B      1.00  8.51

I would like a fourth column, call it Bval that looks as follows:

I'm sure this can't be too hard, but I'm a total Stata newbie, have been Googling for an hour now and can't seem to figure it out.

Upvotes: 1

Views: 196

Answers (2)

Matthew White
Matthew White

Reputation: 363

How about something like this?

clear  
input str1 group key val  
A 0.00 1.23  
A 0.25 2.31  
A 0.50 3.10  
A 0.75 4.21  
A 1.00 2.51  
A 0.00 1.43  
B 0.25 1.31  
B 0.50 5.10  
B 0.75 2.21  
B 1.00 8.51  
end  

preserve  

keep if group == "B"  
rename val Bval  
tempfile b  
save `b'  

restore  

merge m:1 key using `b', nogenerate keep(1 3)  
replace Bval = . if group == "B"

Upvotes: 2

Nick Cox
Nick Cox

Reputation: 37208

This works too for your example. 

 . clear 

 . input str1 group  key   val

             group        key        val
 1. A      0.00  1.23
 2. A      0.25  2.31
 3. A      0.50  3.10
 4. A      0.75  4.21
 5. A      1.00  2.51
 6. A      0.00  1.43
 7. B      0.25  1.31
 8. B      0.50  5.10
 9. B      0.75  2.21
10. B      1.00  8.51
11. end 

. sort key group

. gen Bval = val[_n+1] if group == "A" & group[_n+1] == "B" & key == key[_n+1] 
(6 missing values generated)

. l

      +---------------------------+
      | group   key    val   Bval |
      |---------------------------|
   1. |     A     0   1.23      . |
   2. |     A     0   1.43      . |
   3. |     A   .25   2.31   1.31 |
   4. |     B   .25   1.31      . |
   5. |     A    .5    3.1    5.1 |
      |---------------------------|
   6. |     B    .5    5.1      . |
   7. |     A   .75   4.21   2.21 |
   8. |     B   .75   2.21      . |
   9. |     A     1   2.51   8.51 |
  10. |     B     1   8.51      . |
      +---------------------------+

Upvotes: 2

Related Questions