Merge the data by condition

[Jonas Val]

Hi everyone, I have investors' portfolio dataset 1 as follows. id represents different investors; stock is the name of the stocks; share is the number of shares s/he has; buy_sell is the trading directions. The original dataset has more than 1000 investors and different stocks in more than 3 years.

Now I would like to merge the dataset 1 to another dataset 2, which has the price for each stock over the years as time series.

The logic is, for each id in dataset 1, I would like to get each stock's price from dataset 2 after the investor bought the stock. Even if the person sold the stock at the month x or the datapoint in dataset 1 stopped at month x, I still would like to get stock's price in the next 12 months from dataset 2.So the merged data for investor A should look like this:

Not sure if it is possible to do that in Stata. If anyone could help, it would be great. Thank you very much in advance.

Dataset 1:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 id str12 stock float(month share buy_sell)
"A" "Apple"  688 180 1
"A" "Apple"  689 180 .
"A" "Apple"  690   . 0
"A" "Amazon" 697  10 1
"A" "Amazon" 698  10 .
"A" "Amazon" 699  10 .
"B" "Apple"  682  12 1
"B" "Apple"  683  12 .
"B" "Apple"  684  12 .
"B" "Apple"  685   . 0
"C" "Apple"  688 100 1
"C" "Apple"  689 100 .
"C" "Apple"  690   . 0
"C" "Amazon" 699  20 1
"C" "Amazon" 700   . 0
"C" "Amazon" 701  20 1
"C" "Amazon" 702   . 0
end
label values buy_sell buy_sell1
label def buy_sell1 0 "sell", modify
label def buy_sell1 1 "buy", modify
format %tm month

Dataset 2:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 stock float(month price)
"Apple"  682 10
"Apple"  683 11
"Apple"  684 12
"Apple"  685 13
"Apple"  686 11
"Apple"  687 12
"Apple"  688 11
"Apple"  689 11
"Apple"  690  2
"Apple"  691  3
"Apple"  692  4
"Apple"  693  5
"Apple"  694 22
"Apple"  695 11
"Apple"  696 11
"Apple"  697 22
"Apple"  698 11
"Apple"  699 33
"Apple"  700 22
"Apple"  701 11
"Apple"  702 11
"Amazon" 697  3
"Amazon" 698  4
"Amazon" 699  2
"Amazon" 700  1
"Amazon" 701  4
"Amazon" 702  2
"Amazon" 703  5
"Amazon" 704  3
"Amazon" 705  6
"Amazon" 706  3
"Amazon" 707  6
"Amazon" 708  3
"Amazon" 709  6
"Amazon" 710  4
"Amazon" 711  6
"Amazon" 712  4
"Amazon" 713  6
"Amazon" 714  4
end
format %tm month

[Jonas Val]

Can anyone help? Thank you.

[Andrew Musau]

I don't see any complications here. You do not have to save the price dataset in a temporary file as I do here, just refer to it directly, e.g., "using price.dta" instead of "using `price' " in the highlighted code. Of course, you require that stock and month uniquely identify the observations in the price dataset.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 stock float(month price)
"Apple"  682 10
"Apple"  683 11
"Apple"  684 12
"Apple"  685 13
"Apple"  686 11
"Apple"  687 12
"Apple"  688 11
"Apple"  689 11
"Apple"  690  2
"Apple"  691  3
"Apple"  692  4
"Apple"  693  5
"Apple"  694 22
"Apple"  695 11
"Apple"  696 11
"Apple"  697 22
"Apple"  698 11
"Apple"  699 33
"Apple"  700 22
"Apple"  701 11
"Apple"  702 11
"Amazon" 697  3
"Amazon" 698  4
"Amazon" 699  2
"Amazon" 700  1
"Amazon" 701  4
"Amazon" 702  2
"Amazon" 703  5
"Amazon" 704  3
"Amazon" 705  6
"Amazon" 706  3
"Amazon" 707  6
"Amazon" 708  3
"Amazon" 709  6
"Amazon" 710  4
"Amazon" 711  6
"Amazon" 712  4
"Amazon" 713  6
"Amazon" 714  4
end
format %tm month

isid stock month
tempfile price
save `price'

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 id str12 stock float(month share buy_sell)
"A" "Apple"  688 180 1
"A" "Apple"  689 180 .
"A" "Apple"  690   . 0
"A" "Amazon" 697  10 1
"A" "Amazon" 698  10 .
"A" "Amazon" 699  10 .
"B" "Apple"  682  12 1
"B" "Apple"  683  12 .
"B" "Apple"  684  12 .
"B" "Apple"  685   . 0
"C" "Apple"  688 100 1
"C" "Apple"  689 100 .
"C" "Apple"  690   . 0
"C" "Amazon" 699  20 1
"C" "Amazon" 700   . 0
"C" "Amazon" 701  20 1
"C" "Amazon" 702   . 0
end
label values buy_sell buy_sell1
label def buy_sell1 0 "sell", modify
label def buy_sell1 1 "buy", modify
format %tm month

merge m:1 stock month using `price', keep(master match) nogen

Res.:

. sort id stock month

. l, sepby(id)

     +--------------------------------------------------+
     | id    stock     month   share   buy_sell   price |
     |--------------------------------------------------|
  1. |  A   Amazon    2018m2      10        buy       3 |
  2. |  A   Amazon    2018m3      10          .       4 |
  3. |  A   Amazon    2018m4      10          .       2 |
  4. |  A    Apple    2017m5     180        buy      11 |
  5. |  A    Apple    2017m6     180          .      11 |
  6. |  A    Apple    2017m7       .       sell       2 |
     |--------------------------------------------------|
  7. |  B    Apple   2016m11      12        buy      10 |
  8. |  B    Apple   2016m12      12          .      11 |
  9. |  B    Apple    2017m1      12          .      12 |
 10. |  B    Apple    2017m2       .       sell      13 |
     |--------------------------------------------------|
 11. |  C   Amazon    2018m4      20        buy       2 |
 12. |  C   Amazon    2018m5       .       sell       1 |
 13. |  C   Amazon    2018m6      20        buy       4 |
 14. |  C   Amazon    2018m7       .       sell       2 |
 15. |  C    Apple    2017m5     100        buy      11 |
 16. |  C    Apple    2017m6     100          .      11 |
 17. |  C    Apple    2017m7       .       sell       2 |
     +--------------------------------------------------+

[Jonas Val]

I don't see any complications here. You do not have to save the price dataset in a temporary file as I do here, just refer to it directly, e.g., "using price.dta" instead of "using `price' " in the highlighted code. Of course, you require that stock and month uniquely identify the observations in the price dataset.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 stock float(month price)
"Apple" 682 10
"Apple" 683 11
"Apple" 684 12
"Apple" 685 13
"Apple" 686 11
"Apple" 687 12
"Apple" 688 11
"Apple" 689 11
"Apple" 690 2
"Apple" 691 3
"Apple" 692 4
"Apple" 693 5
"Apple" 694 22
"Apple" 695 11
"Apple" 696 11
"Apple" 697 22
"Apple" 698 11
"Apple" 699 33
"Apple" 700 22
"Apple" 701 11
"Apple" 702 11
"Amazon" 697 3
"Amazon" 698 4
"Amazon" 699 2
"Amazon" 700 1
"Amazon" 701 4
"Amazon" 702 2
"Amazon" 703 5
"Amazon" 704 3
"Amazon" 705 6
"Amazon" 706 3
"Amazon" 707 6
"Amazon" 708 3
"Amazon" 709 6
"Amazon" 710 4
"Amazon" 711 6
"Amazon" 712 4
"Amazon" 713 6
"Amazon" 714 4
end
format %tm month

isid stock month
tempfile price
save `price'

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 id str12 stock float(month share buy_sell)
"A" "Apple" 688 180 1
"A" "Apple" 689 180 .
"A" "Apple" 690 . 0
"A" "Amazon" 697 10 1
"A" "Amazon" 698 10 .
"A" "Amazon" 699 10 .
"B" "Apple" 682 12 1
"B" "Apple" 683 12 .
"B" "Apple" 684 12 .
"B" "Apple" 685 . 0
"C" "Apple" 688 100 1
"C" "Apple" 689 100 .
"C" "Apple" 690 . 0
"C" "Amazon" 699 20 1
"C" "Amazon" 700 . 0
"C" "Amazon" 701 20 1
"C" "Amazon" 702 . 0
end
label values buy_sell buy_sell1
label def buy_sell1 0 "sell", modify
label def buy_sell1 1 "buy", modify
format %tm month

merge m:1 stock month using `price', keep(master match) nogen

Res.:

. sort id stock month

. l, sepby(id)

+--------------------------------------------------+
| id stock month share buy_sell price |
|--------------------------------------------------|
1. | A Amazon 2018m2 10 buy 3 |
2. | A Amazon 2018m3 10 . 4 |
3. | A Amazon 2018m4 10 . 2 |
4. | A Apple 2017m5 180 buy 11 |
5. | A Apple 2017m6 180 . 11 |
6. | A Apple 2017m7 . sell 2 |
|--------------------------------------------------|
7. | B Apple 2016m11 12 buy 10 |
8. | B Apple 2016m12 12 . 11 |
9. | B Apple 2017m1 12 . 12 |
10. | B Apple 2017m2 . sell 13 |
|--------------------------------------------------|
11. | C Amazon 2018m4 20 buy 2 |
12. | C Amazon 2018m5 . sell 1 |
13. | C Amazon 2018m6 20 buy 4 |
14. | C Amazon 2018m7 . sell 2 |
15. | C Apple 2017m5 100 buy 11 |
16. | C Apple 2017m6 100 . 11 |
17. | C Apple 2017m7 . sell 2 |
+--------------------------------------------------+

Thank you, Andrew. Unfortunately, what I would like to merge is different. The logic is, for each id in dataset 1, I would like to get each stock's price from dataset 2 after the investor bought the stock. Even if the person sold the stock at the month x or the datapoint in dataset 1 stopped at month x, I still would like to get stock's price in the next 12 months from dataset 2.So the merged data for investor A should look like this below (as you can see, the merged data is extended a lot for id A from dataset 1):

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 stock float(month price)
"Apple"  682 10
"Apple"  683 11
"Apple"  684 12
"Apple"  685 13
"Apple"  686 11
"Apple"  687 12
"Apple"  688 11
"Apple"  689 11
"Apple"  690  2
"Apple"  691  3
"Apple"  692  4
"Apple"  693  5
"Apple"  694 22
"Apple"  695 11
"Apple"  696 11
"Apple"  697 22
"Apple"  698 11
"Apple"  699 33
"Apple"  700 22
"Apple"  701 11
"Apple"  702 11
"Amazon" 697  3
"Amazon" 698  4
"Amazon" 699  2
"Amazon" 700  1
"Amazon" 701  4
"Amazon" 702  2
"Amazon" 703  5
"Amazon" 704  3
"Amazon" 705  6
"Amazon" 706  3
"Amazon" 707  6
"Amazon" 708  3
"Amazon" 709  6
"Amazon" 710  4
"Amazon" 711  6
"Amazon" 712  4
"Amazon" 713  6
"Amazon" 714  4
end
format %tm month

isid stock month
tempfile price
save `price'

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 id str12 stock float(month share buy_sell)
"A" "Apple"  688 180 1
"A" "Apple"  689 180 .
"A" "Apple"  690   . 0
"A" "Amazon" 697  10 1
"A" "Amazon" 698  10 .
"A" "Amazon" 699  10 .
"B" "Apple"  682  12 1
"B" "Apple"  683  12 .
"B" "Apple"  684  12 .
"B" "Apple"  685   . 0
"C" "Apple"  688 100 1
"C" "Apple"  689 100 .
"C" "Apple"  690   . 0
"C" "Amazon" 699  20 1
"C" "Amazon" 700   . 0
"C" "Amazon" 701  20 1
"C" "Amazon" 702   . 0
end
label values buy_sell buy_sell1
label def buy_sell1 0 "sell", modify
label def buy_sell1 1 "buy", modify
format %tm month


egen sid= group(id stock), label
xtset sid month
tsfill, full
foreach var in id stock{
    bys sid (month): replace `var'= `var'[_n-1] if missing(`var') & !missing(`var'[_n-1])
}
drop if missing(id)
merge m:1 stock month using `price', keep(master match) nogen

Res.:

. sort id stock month

. l, sepby(id)

     +-------------------------------------------------------------+
     | id    stock     month   share   buy_sell        sid   price |
     |-------------------------------------------------------------|
  1. |  A   Amazon    2018m2      10        buy   A Amazon       3 |
  2. |  A   Amazon    2018m3      10          .   A Amazon       4 |
  3. |  A   Amazon    2018m4      10          .   A Amazon       2 |
  4. |  A   Amazon    2018m5       .          .   A Amazon       1 |
  5. |  A   Amazon    2018m6       .          .   A Amazon       4 |
  6. |  A   Amazon    2018m7       .          .   A Amazon       2 |
  7. |  A    Apple    2017m5     180        buy    A Apple      11 |
  8. |  A    Apple    2017m6     180          .    A Apple      11 |
  9. |  A    Apple    2017m7       .       sell    A Apple       2 |
 10. |  A    Apple    2017m8       .          .    A Apple       3 |
 11. |  A    Apple    2017m9       .          .    A Apple       4 |
 12. |  A    Apple   2017m10       .          .    A Apple       5 |
 13. |  A    Apple   2017m11       .          .    A Apple      22 |
 14. |  A    Apple   2017m12       .          .    A Apple      11 |
 15. |  A    Apple    2018m1       .          .    A Apple      11 |
 16. |  A    Apple    2018m2       .          .    A Apple      22 |
 17. |  A    Apple    2018m3       .          .    A Apple      11 |
 18. |  A    Apple    2018m4       .          .    A Apple      33 |
 19. |  A    Apple    2018m5       .          .    A Apple      22 |
 20. |  A    Apple    2018m6       .          .    A Apple      11 |
 21. |  A    Apple    2018m7       .          .    A Apple      11 |
     |-------------------------------------------------------------|
 22. |  B    Apple   2016m11      12        buy    B Apple      10 |
 23. |  B    Apple   2016m12      12          .    B Apple      11 |
 24. |  B    Apple    2017m1      12          .    B Apple      12 |
 25. |  B    Apple    2017m2       .       sell    B Apple      13 |
 26. |  B    Apple    2017m3       .          .    B Apple      11 |
 27. |  B    Apple    2017m4       .          .    B Apple      12 |
 28. |  B    Apple    2017m5       .          .    B Apple      11 |
 29. |  B    Apple    2017m6       .          .    B Apple      11 |
 30. |  B    Apple    2017m7       .          .    B Apple       2 |
 31. |  B    Apple    2017m8       .          .    B Apple       3 |
 32. |  B    Apple    2017m9       .          .    B Apple       4 |
 33. |  B    Apple   2017m10       .          .    B Apple       5 |
 34. |  B    Apple   2017m11       .          .    B Apple      22 |
 35. |  B    Apple   2017m12       .          .    B Apple      11 |
 36. |  B    Apple    2018m1       .          .    B Apple      11 |
 37. |  B    Apple    2018m2       .          .    B Apple      22 |
 38. |  B    Apple    2018m3       .          .    B Apple      11 |
 39. |  B    Apple    2018m4       .          .    B Apple      33 |
 40. |  B    Apple    2018m5       .          .    B Apple      22 |
 41. |  B    Apple    2018m6       .          .    B Apple      11 |
 42. |  B    Apple    2018m7       .          .    B Apple      11 |
     |-------------------------------------------------------------|
 43. |  C   Amazon    2018m4      20        buy   C Amazon       2 |
 44. |  C   Amazon    2018m5       .       sell   C Amazon       1 |
 45. |  C   Amazon    2018m6      20        buy   C Amazon       4 |
 46. |  C   Amazon    2018m7       .       sell   C Amazon       2 |
 47. |  C    Apple    2017m5     100        buy    C Apple      11 |
 48. |  C    Apple    2017m6     100          .    C Apple      11 |
 49. |  C    Apple    2017m7       .       sell    C Apple       2 |
 50. |  C    Apple    2017m8       .          .    C Apple       3 |
 51. |  C    Apple    2017m9       .          .    C Apple       4 |
 52. |  C    Apple   2017m10       .          .    C Apple       5 |
 53. |  C    Apple   2017m11       .          .    C Apple      22 |
 54. |  C    Apple   2017m12       .          .    C Apple      11 |
 55. |  C    Apple    2018m1       .          .    C Apple      11 |
 56. |  C    Apple    2018m2       .          .    C Apple      22 |
 57. |  C    Apple    2018m3       .          .    C Apple      11 |
 58. |  C    Apple    2018m4       .          .    C Apple      33 |
 59. |  C    Apple    2018m5       .          .    C Apple      22 |
 60. |  C    Apple    2018m6       .          .    C Apple      11 |
 61. |  C    Apple    2018m7       .          .    C Apple      11 |
     +-------------------------------------------------------------+

.

[Hemanshu Kumar]

The logic is, for each id in dataset 1, I would like to get each stock's price from dataset 2 after the investor bought the stock. Even if the person sold the stock at the month x or the datapoint in dataset 1 stopped at month x, I still would like to get stock's price in the next 12 months from dataset 2.

The solution in #5 does not appear to satisfy the second statement in OP's post in #4. Here is some modified code that builds on #5:

sort id stock month
by id stock (month): gen max_month = month[_N] + 12
by id stock: gen byte to_expand = (_n == _N)
expand 2 if to_expand, gen(new)
foreach var in share buy_sell {
    replace `var' = . if new
}
replace month = max_month if new
drop max_month to_expand new

egen sid= group(id stock), label
xtset sid month

tsfill, full

foreach var in id stock{
    bys sid (month): replace `var'= `var'[_n-1] if missing(`var') & !missing(`var'[_n-1])
}
drop if missing(id)
merge m:1 stock month using `price', keep(master match) nogen
sort id stock month
drop if missing(price) & missing(buy_sell) & missing(share)
drop sid

which produces:

  +--------------------------------------------------+
  | id    stock     month   share   buy_sell   price |
  |--------------------------------------------------|
  |  A   Amazon    2018m2      10        buy       3 |
  |  A   Amazon    2018m3      10          .       4 |
  |  A   Amazon    2018m4      10          .       2 |
  |  A   Amazon    2018m5       .          .       1 |
  |  A   Amazon    2018m6       .          .       4 |
  |  A   Amazon    2018m7       .          .       2 |
  |  A   Amazon    2018m8       .          .       5 |
  |  A   Amazon    2018m9       .          .       3 |
  |  A   Amazon   2018m10       .          .       6 |
  |  A   Amazon   2018m11       .          .       3 |
  |  A   Amazon   2018m12       .          .       6 |
  |  A   Amazon    2019m1       .          .       3 |
  |  A   Amazon    2019m2       .          .       6 |
  |  A   Amazon    2019m3       .          .       4 |
  |  A   Amazon    2019m4       .          .       6 |
  |  A   Amazon    2019m5       .          .       4 |
  |  A   Amazon    2019m6       .          .       6 |
  |  A   Amazon    2019m7       .          .       4 |
  |--------------------------------------------------|
  |  A    Apple    2017m5     180        buy      11 |
  |  A    Apple    2017m6     180          .      11 |
  |  A    Apple    2017m7       .       sell       2 |
  |  A    Apple    2017m8       .          .       3 |
  |  A    Apple    2017m9       .          .       4 |
  |  A    Apple   2017m10       .          .       5 |
  |  A    Apple   2017m11       .          .      22 |
  |  A    Apple   2017m12       .          .      11 |
  |  A    Apple    2018m1       .          .      11 |
  |  A    Apple    2018m2       .          .      22 |
  |  A    Apple    2018m3       .          .      11 |
  |  A    Apple    2018m4       .          .      33 |
  |  A    Apple    2018m5       .          .      22 |
  |  A    Apple    2018m6       .          .      11 |
  |  A    Apple    2018m7       .          .      11 |
  |--------------------------------------------------|
  |  B    Apple   2016m11      12        buy      10 |
  |  B    Apple   2016m12      12          .      11 |
  |  B    Apple    2017m1      12          .      12 |
  |  B    Apple    2017m2       .       sell      13 |
  |  B    Apple    2017m3       .          .      11 |
  |  B    Apple    2017m4       .          .      12 |
  |  B    Apple    2017m5       .          .      11 |
  |  B    Apple    2017m6       .          .      11 |
  |  B    Apple    2017m7       .          .       2 |
  |  B    Apple    2017m8       .          .       3 |
  |  B    Apple    2017m9       .          .       4 |
  |  B    Apple   2017m10       .          .       5 |
  |  B    Apple   2017m11       .          .      22 |
  |  B    Apple   2017m12       .          .      11 |
  |  B    Apple    2018m1       .          .      11 |
  |  B    Apple    2018m2       .          .      22 |
  |  B    Apple    2018m3       .          .      11 |
  |  B    Apple    2018m4       .          .      33 |
  |  B    Apple    2018m5       .          .      22 |
  |  B    Apple    2018m6       .          .      11 |
  |  B    Apple    2018m7       .          .      11 |
  |--------------------------------------------------|
  |  C   Amazon    2018m4      20        buy       2 |
  |  C   Amazon    2018m5       .       sell       1 |
  |  C   Amazon    2018m6      20        buy       4 |
  |  C   Amazon    2018m7       .       sell       2 |
  |  C   Amazon    2018m8       .          .       5 |
  |  C   Amazon    2018m9       .          .       3 |
  |  C   Amazon   2018m10       .          .       6 |
  |  C   Amazon   2018m11       .          .       3 |
  |  C   Amazon   2018m12       .          .       6 |
  |  C   Amazon    2019m1       .          .       3 |
  |  C   Amazon    2019m2       .          .       6 |
  |  C   Amazon    2019m3       .          .       4 |
  |  C   Amazon    2019m4       .          .       6 |
  |  C   Amazon    2019m5       .          .       4 |
  |  C   Amazon    2019m6       .          .       6 |
  |  C   Amazon    2019m7       .          .       4 |
  |--------------------------------------------------|
  |  C    Apple    2017m5     100        buy      11 |
  |  C    Apple    2017m6     100          .      11 |
  |  C    Apple    2017m7       .       sell       2 |
  |  C    Apple    2017m8       .          .       3 |
  |  C    Apple    2017m9       .          .       4 |
  |  C    Apple   2017m10       .          .       5 |
  |  C    Apple   2017m11       .          .      22 |
  |  C    Apple   2017m12       .          .      11 |
  |  C    Apple    2018m1       .          .      11 |
  |  C    Apple    2018m2       .          .      22 |
  |  C    Apple    2018m3       .          .      11 |
  |  C    Apple    2018m4       .          .      33 |
  |  C    Apple    2018m5       .          .      22 |
  |  C    Apple    2018m6       .          .      11 |
  |  C    Apple    2018m7       .          .      11 |
  +--------------------------------------------------+

[Jonas Val]

Thanks a lot.