first date with higher price

[River Huang]

Dear All, Suppose that I have the following data set

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id int Date float price
1 21914 10.67
1 21937 10.22
1 21973   9.7
1 22005  9.07
1 22035  9.04
1 22064  9.72
1 22096 11.47
1 22127 13.19
1 22158 12.28
1 22188 12.14
1 22218 10.05
1 22249    10
2 21914  7.45
2 21937  7.31
2 21973  7.73
2 22005  6.86
2 22035  6.71
2 22064  6.73
2 22096  7.97
2 22127  9.91
2 22158  8.87
2 22188  8.02
2 22218  8.06
2 22249  7.68
3 21914  4.14
3 21937  3.96
3 21973  3.54
3 22005  3.36
3 22035  3.39
3 22064   3.3
end
format %tdCCYY-NN-DD Date
label values id name
label def name 1 "000020", modify
label def name 2 "000045", modify
label def name 3 "000056", modify

Firstly, I use the code

bys id (Date): egen ip = first(price)

to find the initial price for each id. Then, for each id, I'd like to find the (first) Date with higher price than the initial price (ip). Any suggestions? Thanks.

[Andrew Musau]

bys id (Date): gen wanted= sum(sum(price>price[1]))==1

Res.:

. bys id (Date): gen wanted= sum(sum(price>price[1]))==1

. l, sepby(id)

     +--------------------------------------+
     |     id         Date   price   wanted |
     |--------------------------------------|
  1. | 000020   2019-12-31   10.67        0 |
  2. | 000020   2020-01-23   10.22        0 |
  3. | 000020   2020-02-28     9.7        0 |
  4. | 000020   2020-03-31    9.07        0 |
  5. | 000020   2020-04-30    9.04        0 |
  6. | 000020   2020-05-29    9.72        0 |
  7. | 000020   2020-06-30   11.47        1 |
  8. | 000020   2020-07-31   13.19        0 |
  9. | 000020   2020-08-31   12.28        0 |
 10. | 000020   2020-09-30   12.14        0 |
 11. | 000020   2020-10-30   10.05        0 |
 12. | 000020   2020-11-30      10        0 |
     |--------------------------------------|
 13. | 000045   2019-12-31    7.45        0 |
 14. | 000045   2020-01-23    7.31        0 |
 15. | 000045   2020-02-28    7.73        1 |
 16. | 000045   2020-03-31    6.86        0 |
 17. | 000045   2020-04-30    6.71        0 |
 18. | 000045   2020-05-29    6.73        0 |
 19. | 000045   2020-06-30    7.97        0 |
 20. | 000045   2020-07-31    9.91        0 |
 21. | 000045   2020-08-31    8.87        0 |
 22. | 000045   2020-09-30    8.02        0 |
 23. | 000045   2020-10-30    8.06        0 |
 24. | 000045   2020-11-30    7.68        0 |
     |--------------------------------------|
 25. | 000056   2019-12-31    4.14        0 |
 26. | 000056   2020-01-23    3.96        0 |
 27. | 000056   2020-02-28    3.54        0 |
 28. | 000056   2020-03-31    3.36        0 |
 29. | 000056   2020-04-30    3.39        0 |
 30. | 000056   2020-05-29     3.3        0 |
     +--------------------------------------+

.

[Nick Cox]

The first date is the minimum for each identifier over dates satisfying that condition, so code could be something like

by id : egen wanted = min(cond(price > ip & price < ., date, .))

See also Section 9 of https://www.stata-journal.com/articl...article=dm0055

Note that first() here is a community-contributed function for egen from the egenmore package on SSC, as you are asked to explain.

[River Huang]

Dear @Andrew Musau and @Nick Cox, Many thanks for your insightful suggestions.