Hi,
I have difficulties in replacing certain missing values with a calculation based on two other cells. In order to explain, let me use a sketched example. In my data set I have missing values for the variable FR in 2011 and 2012 for every observed unit, denoted ID. To explain the mechanism of the variable: FRt takes the value FRt-1 - DFt. As we see below, for ID#1, FR2010 is FR2009 - DF2010. I need to generate a new FR variable (or replace missing values) that for every missing value in 2011 and 2012, by ID, take the cell above [_n-1] minus the cell to the left. However, I am uncertain how to refer to the cell to the left properly - I presume I can utilise the row number.
I hope this explanation was clear.
I would guess I could start my code with the following:
Thanks in advance for any help.
I have difficulties in replacing certain missing values with a calculation based on two other cells. In order to explain, let me use a sketched example. In my data set I have missing values for the variable FR in 2011 and 2012 for every observed unit, denoted ID. To explain the mechanism of the variable: FRt takes the value FRt-1 - DFt. As we see below, for ID#1, FR2010 is FR2009 - DF2010. I need to generate a new FR variable (or replace missing values) that for every missing value in 2011 and 2012, by ID, take the cell above [_n-1] minus the cell to the left. However, I am uncertain how to refer to the cell to the left properly - I presume I can utilise the row number.
ID | Year | DF | FR |
1 | 2009 | 0 | 1 |
1 | 2010 | 0.50 | 0.50 |
1 | 2011 | 0.25 | (missing) |
1 | 2012 | 0 | (missing) |
2 | 2009 | 0.30 | 0.70 |
2 | 2010 | 0.40 | 0.30 |
2 | 2011 | 0 | (missing) |
2 | 2012 | 0.15 | (missing) |
3 | 2009 | 0 | 1 |
3 | 2010 | 0 | 1 |
3 | 2011 | 0.90 | (missing) |
3 | 2012 | 0.07 | (missing) |
I hope this explanation was clear.
I would guess I could start my code with the following:
Code:
bysort ID (Year) : [...]
Thanks in advance for any help.
Comment