Dear Statalist,

I have a dataset that contains the entry dates of a certain investor (unique InvestorID) in a company (unique CompanyID). In order to generate an experience variable, I would like to count the number of times the investor encountered a certain event with previous companies (event id and event date) it has entered (entry date and entry event id) prior to the point in time at which the investor entered the company of interest. In the example below investor 1 should have an event experience count of 2 when entering in company 2 and investor 2 should have an experience count of 3 when entering in company 4. In my example below I have manually added the experience variable that I want to generate, but have not found how to put this into practice.
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(companyid investorid entryeventid eventid) float(eventdate entrydate experience)
1 1 1  1 18130 16973 .
1 1 1  2 18600 16973 .
1 1 1  3 18904 16973 .
1 1 1  4 19281 16973 .
2 1 2  5 18888 18696 2
2 1 2  6 18918 18696 2
2 1 2  7 19226 18696 2
2 1 2  8 20048 18696 2
3 2 3  9 18101 16608 .
3 2 3 10 18133 16608 .
3 2 3 11 18993 16608 .
4 2 4 12 20843 20750 3
end
format %td eventdate
format %td entrydate
What I have tried so far is to work with rangestat:
Code:
gen one = 1
rangestat (sum) one, int(entrydate -3650 -1) by(investorid)
rename one_sum experience
However, in the case of company 2 and investorid 1, it also counts the events in company 1 after the investor has entered company 2. I understand why, since my code only considers the entry dates. However, I have not found a solution to solve this issue. I thought I should work with some kind of "if" option, but this is not allowed by rangestat.

Could anyone help me with my code?

Thank you in advance.

Kind regards,

Gianni