CODEWITHSUNDEEP
stata
user42459
user42459

Reputation: 915

Merging two variables

I have the following data:

enter image description here

I want to turn the data in the upper panel into the data in the lower one.

For each origin group, I want to add one line with destination value -1, and var value from varnew.

I tried to find if there is a command which adds one row so that I can do something like:

bysort origin: addrow

However, it seems there isn't any such thing.

Upvotes: 0

Views: 77

Answers (1)

user8682794
user8682794

Reputation:

Using your toy example data:

clear

input destination origin var varnew
0 111 124 .
111 111 671 168
0 222 623 .
222 222 768 865
end

list, abbreviate(15)

     +-------------------------------------+
     | destination   origin   var   varnew |
     |-------------------------------------|
  1. |           0      111   124        . |
  2. |         111      111   671      168 |
  3. |           0      222   623        . |
  4. |         222      222   768      865 |
     +-------------------------------------+

expand 2 if varnew != .
sort origin destination 
list, abbreviate(15)

     +-------------------------------------+
     | destination   origin   var   varnew |
     |-------------------------------------|
  1. |           0      111   124        . |
  2. |         111      111   671      168 |
  3. |         111      111   671      168 |
  4. |           0      222   623        . |
  5. |         222      222   768      865 |
     |-------------------------------------|
  6. |         222      222   768      865 |
     +-------------------------------------+

The following works for me:

bysort origin: replace destination = -1 if destination[_n] == destination[_n+1] & !missing(varnew)
bysort origin: replace var = varnew if var[_n] == var[_n+1] & !missing(varnew)

list destination origin var, abbreviate(15)

     +----------------------------+
     | destination   origin   var |
     |----------------------------|
  1. |           0      111   124 |
  2. |          -1      111   168 |
  3. |         111      111   671 |
  4. |           0      222   623 |
  5. |          -1      222   865 |
     |----------------------------|
  6. |         222      222   768 |
     +----------------------------+

Upvotes: 2

Related Questions