In Stata
- Daily price data,
permnois the company identifier - For each
permno month, I want price end of last month
tsset permno date
gen m= mofd(date)
format m %tm
* price end of each month
bys permno m: gen prc_end= prc[_N]
* price end of the prior month
gen n=m-1
format n %tm
bys permno m: gen prc_endpm= prc_end if n==m[_n-1]
* no results
Current Data:
permno date prc m prc_end n
10026 24-Jan-19 145.8000031 2019m1 154.35 2018m12
10026 25-Jan-19 144.5500031 2019m1 154.35 2018m12
10026 28-Jan-19 140 2019m1 154.35 2018m12
10026 29-Jan-19 156.8200073 2019m1 154.35 2018m12
10026 30-Jan-19 150.5 2019m1 154.35 2018m12
10026 31-Jan-19 154.3500061 2019m1 154.35 2018m12
10026 01-Feb-19 154.8000031 2019m2 155.28 2019m1
10026 04-Feb-19 158.4400024 2019m2 155.28 2019m1
10026 05-Feb-19 158.2599945 2019m2 155.28 2019m1
10026 06-Feb-19 158.2400055 2019m2 155.28 2019m1
10026 07-Feb-19 156.4100037 2019m2 155.28 2019m1
>Solution :
To make things clearer, consider a silly example dataset.
clear
set obs 6
gen permno = 1
gen date = mdy(1 + (_n > 3), real(word("1 15 31 1 15 28", _n)), 2022)
format date %td
gen m = mofd(date)
format m %tm
gen n = m - 1
format n %tm
gen price = _n
list, sepby(permno m)
+-----------------------------------------------+
| permno date m n price |
|-----------------------------------------------|
1. | 1 01jan2022 2022m1 2021m12 1 |
2. | 1 15jan2022 2022m1 2021m12 2 |
3. | 1 31jan2022 2022m1 2021m12 3 |
|-----------------------------------------------|
4. | 1 01feb2022 2022m2 2022m1 4 |
5. | 1 15feb2022 2022m2 2022m1 5 |
6. | 1 28feb2022 2022m2 2022m1 6 |
+-----------------------------------------------+
Now
bys permno m: gen prc_end= price[_N]
will probably work, but this would be safer:
bys permno m (date): gen prc_end= price[_N]
Things go wrong when you go
bys permno m: gen prc_endpm= prc_end if n==m[_n-1]
The effect of the by: is to confine calculations to blocks with the same permno and monthly date. [_n-1] here is legal but it refers to the previous observation in the same block of observations (usefully if there is one; if there isn’t the code is still legal).
You want [_n-1] to refer to the previous month (and the same permno) but that is not what your syntax means. Also, the example shows that, although your syntax is legal, there are no observations that satisfy your if condition, as m and n are never equal within the same block of observations.
What you want can be done with by: but you need to look across months.
This should do it:
bysort permno (m date) : gen previous = price[_n-1] if m[_n-1] == m -1
bysort permno m (date) : replace previous = previous[1]
list, sepby(permno m)
+----------------------------------------------------------+
| permno date m n price previous |
|----------------------------------------------------------|
1. | 1 01jan2022 2022m1 2021m12 1 . |
2. | 1 15jan2022 2022m1 2021m12 2 . |
3. | 1 31jan2022 2022m1 2021m12 3 . |
|----------------------------------------------------------|
4. | 1 01feb2022 2022m2 2022m1 4 3 |
5. | 1 15feb2022 2022m2 2022m1 5 3 |
6. | 1 28feb2022 2022m2 2022m1 6 3 |
+----------------------------------------------------------+