I want to capture in a variable (called “absolutedistance”) the absolute value of the distance of a numeric variable (called “v1”) between a given subject (identified with var “id”; total 50 subjects) in a given year (identified with var “duration”; total 13 years) that is susceptible to get sick (to experience the “event”, captured by the dependent/outcome binary var called “sick”; coded 1 when a subject experiences the event in a given year, 0 otherwise) and the value of “v1” of all the subjects that got sick (subjects that have previously experienced the event).
The value for the subjects that previously fell sick is the subjects’ value of “v1” at the time of experiencing the event (in the year when the subjects got sick). The value of “v1” at the time of falling sick is a weighted moving average wherein the most recent subject to fall sick has the same weight as all other sick subjects. Thus, for each year in the data set, I wish to calculate the distance ( “absolutedistance”) for each healthy subject using the following formula:
“sick” can be experienced more than once, but for simplicity, let us focus on a data set where we are interested in the time until the first “sick” (obs. after the first event would be “right censored” and dropped from the analysis; the data will be analyzed with survival/duration techniques).
Now, things get more complicated when we consider that some subjects experienced the event (got sick) in the same year (let´s identify these as “ties”). “ties” contribute to the “average of v1 of all other subjects that fell sick before” with the “average of v1 of those ties”. E.g., three subjects fell sick in year 2000. Those “ties” will contribute to the “average of v1 of all other subjects that fell sick before” from year 2001 onwards with the average of v1 of those three subjects in year 2000.
I will appreciate comments on how to compute this in Stata or R.
For a inefficient, error-prone manual workaround to calculate this in excel, see my post:excel workaround
