Dear Statalist Community,

Set-up
I am currently working with a database of executive compensation for CEOs of Fortune 500 companies including their remuneration in options and a variety of other CEO and company specific variables. Each CEO may receive multiple option awards per year and the database is already sorted to group each CEO (through their unique executive/company ID) to their firm in ascending order of year.

Aim
I am trying to identify observations for which the value of the CEO's option awards has increased by at least 40% over the year, denoted by the variable 'OptionMoneynessMIN'. Once identified, I would like to create a consequent binary variable (known as ITM_Exp in my dataset) that identifies, by CEO, if the previous condition on options was fulfilled.

Specifically, with ITM_Exp my goal is 1) create a variable that counts the number of times in a year that an option increased by at least 40% and report this as a single observation for each year by CEO and 2) to create a binary variable that only once identifies if any of the options the CEO held increased by at least 40%. That is, the value should equal 1 at the first occurrence of satisfying the condition and then is subsequently 0 for all other observations of that CEO over his entire tenure.

Variable 2 will later be used to split CEOs in two groups - long holders (those with options that increased by 40%) and normal (those that did not) - to run a logit regression hence why the observations need to be flattened into just one number representing one CEO.

Below is an excerpt of the dataset for clarification:

Code:
* Example generated by -dataex-. For more info, type help dataex
clear
input str58 FullName str50 CompanyName long ExecutiveCompanyCombination double OptionMoneynessMIN float ITM_Exp
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111                   0 0
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .4678522571819427 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .6132562673142037 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111  .03063275651109948 0
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111  .07260613502512689 0
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111  .10826183360647826 0
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .2558993183009962 0
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .3070468654069173 0
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .5705383416998442 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111    .538318073923364 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .7491806439174862 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .5774838928450324 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .8937512721351517 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .8937512721351517 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .6105244936818418 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   3.162178336325553 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .4156319967433339 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .4156319967433339 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .9443569553805773 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   3.433273488928785 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111    1.22968118545128 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   4.943147815679233 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   .9362177534023969 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111   1.501837270341207 1
"Kenneth I. Chenault"    "AMERICAN EXPRESS CO"       111  1.1400987876066455 1
As shown above, this CEO held many options over his tenure, some of which increased by at least 40% - this is identified by ITM_Exp = 1.

My first attempt to solve issue number 1 was to use a bysort method:

Code:
gen longholder = 0
bysort ExecutiveCompanyCombination (Year): replace longholder = 1 if ITM_Exp == 1 & ITM_Exp != .
quietly by ExecutiveCompanyCombination Year: gen dup = cond(_N == 1, 0, _n) if longholder == 1
The solution approached what I wanted, however, retuned the index position of the first occurrence rather than using the first occurrence as the start of the count.

To that end, could you please offer me some guidance on how to solve this problem? Any help would be greatly appreciated, thank you!