Value Replacement

[Andrea Repetto]

Dear all,

I am quite new with stata, and after a couple of days trying to fix a problem without any success I decided to post my problem here.
Basically, I have a panel dataset where permno identifies the companies, and BM indicates the book to market value of each company which is calculated in December of each year.
In accordance with my purpose, for each company the BM for month t from June of year t+1 through May of the year t+2 is taken to be the book to market ratio calculated in December of year t.
Therefore I am not able to replace the BM value calculated in December of year t in June of year t+1 through May t+2.
This replacement should be done for each company as well.
Any help would be really appreciated.
Kind regards

permno	date	year	month	BM
10001	30-Apr-87	1987	4
10001	29-May-87	1987	5
10001	30-Jun-87	1987	6
10001	31-Jul-87	1987	7
10001	31-Aug-87	1987	8
10001	30-Sep-87	1987	9
10001	30-Oct-87	1987	10
10001	30-Nov-87	1987	11
10001	31-Dec-87	1987	12	1.207618
10001	29-Jan-88	1988	1
10001	29-Feb-88	1988	2
10001	31-Mar-88	1988	3
10001	29-Apr-88	1988	4
10001	31-May-88	1988	5
10001	30-Jun-88	1988	6
10001	29-Jul-88	1988	7
10001	31-Aug-88	1988	8
10001	30-Sep-88	1988	9
10001	31-Oct-88	1988	10
10001	30-Nov-88	1988	11
10001	30-Dec-88	1988	12	1.145192
10001	31-Jan-89	1989	1
10001	28-Feb-89	1989	2
10001	31-Mar-89	1989	3
10001	28-Apr-89	1989	4
10001	31-May-89	1989	5

[Nick Cox]

When I copy and paste your data the date variable ends as string because you didn't use dataex (FAQ Advice #12).

No matter, as you surely and sorely need first a monthly date variable and then a variable denoting your years from June to May.

When that's done spreading the December values to all months is immediate.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int permno str9 date int year byte month float bm
10001 "30-Apr-87" 1987  4        .
10001 "29-May-87" 1987  5        .
10001 "30-Jun-87" 1987  6        .
10001 "31-Jul-87" 1987  7        .
10001 "31-Aug-87" 1987  8        .
10001 "30-Sep-87" 1987  9        .
10001 "30-Oct-87" 1987 10        .
10001 "30-Nov-87" 1987 11        .
10001 "31-Dec-87" 1987 12 1.207618
10001 "29-Jan-88" 1988  1        .
10001 "29-Feb-88" 1988  2        .
10001 "31-Mar-88" 1988  3        .
10001 "29-Apr-88" 1988  4        .
10001 "31-May-88" 1988  5        .
10001 "30-Jun-88" 1988  6        .
10001 "29-Jul-88" 1988  7        .
10001 "31-Aug-88" 1988  8        .
10001 "30-Sep-88" 1988  9        .
10001 "31-Oct-88" 1988 10        .
10001 "30-Nov-88" 1988 11        .
10001 "30-Dec-88" 1988 12 1.145192
10001 "31-Jan-89" 1989  1        .
10001 "28-Feb-89" 1989  2        .
10001 "31-Mar-89" 1989  3        .
10001 "28-Apr-89" 1989  4        .
10001 "31-May-89" 1989  5        .
end

gen mdate = ym(year, month)

gen fyear = year if month == 6
bysort permno (mdate) : replace fyear = fyear[_n-1] if missing(fyear)
egen wanted = max(bm), by(fyear)

list, sepby(fyear)

     +------------------------------------------------------------------------+
     | permno        date   year   month        bm   mdate   fyear     wanted |
     |------------------------------------------------------------------------|
  1. |  10001   30-Apr-87   1987       4         .     327       .          . |
  2. |  10001   29-May-87   1987       5         .     328       .          . |
     |------------------------------------------------------------------------|
  3. |  10001   30-Jun-87   1987       6         .     329    1987   1.207618 |
  4. |  10001   31-Jul-87   1987       7         .     330    1987   1.207618 |
  5. |  10001   31-Aug-87   1987       8         .     331    1987   1.207618 |
  6. |  10001   30-Sep-87   1987       9         .     332    1987   1.207618 |
  7. |  10001   30-Oct-87   1987      10         .     333    1987   1.207618 |
  8. |  10001   30-Nov-87   1987      11         .     334    1987   1.207618 |
  9. |  10001   31-Dec-87   1987      12   1.20762     335    1987   1.207618 |
 10. |  10001   29-Jan-88   1988       1         .     336    1987   1.207618 |
 11. |  10001   29-Feb-88   1988       2         .     337    1987   1.207618 |
 12. |  10001   31-Mar-88   1988       3         .     338    1987   1.207618 |
 13. |  10001   29-Apr-88   1988       4         .     339    1987   1.207618 |
 14. |  10001   31-May-88   1988       5         .     340    1987   1.207618 |
     |------------------------------------------------------------------------|
 15. |  10001   30-Jun-88   1988       6         .     341    1988   1.145192 |
 16. |  10001   29-Jul-88   1988       7         .     342    1988   1.145192 |
 17. |  10001   31-Aug-88   1988       8         .     343    1988   1.145192 |
 18. |  10001   30-Sep-88   1988       9         .     344    1988   1.145192 |
 19. |  10001   31-Oct-88   1988      10         .     345    1988   1.145192 |
 20. |  10001   30-Nov-88   1988      11         .     346    1988   1.145192 |
 21. |  10001   30-Dec-88   1988      12   1.14519     347    1988   1.145192 |
 22. |  10001   31-Jan-89   1989       1         .     348    1988   1.145192 |
 23. |  10001   28-Feb-89   1989       2         .     349    1988   1.145192 |
 24. |  10001   31-Mar-89   1989       3         .     350    1988   1.145192 |
 25. |  10001   28-Apr-89   1989       4         .     351    1988   1.145192 |
 26. |  10001   31-May-89   1989       5         .     352    1988   1.145192 |
     +------------------------------------------------------------------------+

.

[Andrea Repetto]

Dear Nicl,
first of all I thank you for your detailed reply and for your availability, unfortunately maybe I explained badly my problem.
Taking as example the table I posted before, my desired result would be the following one:

permno	date	year	month	BM	Wanted
10001	30-Sep-87	1987	9
10001	30-Oct-87	1987	10
10001	30-Nov-87	1987	11
10001	31-Dec-87	1987	12	1.207618
10001	29-Jan-88	1988	1
10001	29-Feb-88	1988	2
10001	31-Mar-88	1988	3
10001	29-Apr-88	1988	4
10001	31-May-88	1988	5
10001	30-Jun-88	1988	6		1.207618
10001	29-Jul-88	1988	7		1.207618
10001	31-Aug-88	1988	8		1.207618
10001	30-Sep-88	1988	9		1.207618
10001	31-Oct-88	1988	10		1.207618
10001	30-Nov-88	1988	11		1.207618
10001	30-Dec-88	1988	12	1.145192	1.207618
10001	31-Jan-89	1989	1		1.207618
10001	28-Feb-89	1989	2		1.207618
10001	31-Mar-89	1989	3		1.207618
10001	28-Apr-89	1989	4		1.207618
10001	31-May-89	1989	5		1.207618
10001	30-Jun-89	1989	6		1.145192
10001	29-Jul-89	1989	7		1.145192
10001	31-Aug-89	1989	8		1.145192
10001	30-Sep-89	1989	9		1.145192

Basically the BM value for each company (calculated in december of year t) is available from June of year t+1 to May of year t+2, then in June of year t+2 each company would have the BM calculated in December of year t+1 and so on..
Hopefully, this can clarify my question.
Best regards
Andrea

[Andrea Repetto]

Dear "Nick"

[Nick Cox]

You do say that. So, for this problem I would reach for rangestat (SSC) which you should install with

ssc install rangestat

* Example generated by -dataex-. To install: ssc install dataex
clear
input int permno str9 date int year byte month float bm
10001 "30-Apr-87" 1987  4        .
10001 "29-May-87" 1987  5        .
10001 "30-Jun-87" 1987  6        .
10001 "31-Jul-87" 1987  7        .
10001 "31-Aug-87" 1987  8        .
10001 "30-Sep-87" 1987  9        .
10001 "30-Oct-87" 1987 10        .
10001 "30-Nov-87" 1987 11        .
10001 "31-Dec-87" 1987 12 1.207618
10001 "29-Jan-88" 1988  1        .
10001 "29-Feb-88" 1988  2        .
10001 "31-Mar-88" 1988  3        .
10001 "29-Apr-88" 1988  4        .
10001 "31-May-88" 1988  5        .
10001 "30-Jun-88" 1988  6        .
10001 "29-Jul-88" 1988  7        .
10001 "31-Aug-88" 1988  8        .
10001 "30-Sep-88" 1988  9        .
10001 "31-Oct-88" 1988 10        .
10001 "30-Nov-88" 1988 11        .
10001 "30-Dec-88" 1988 12 1.145192
10001 "31-Jan-89" 1989  1        .
10001 "28-Feb-89" 1989  2        .
10001 "31-Mar-89" 1989  3        .
10001 "28-Apr-89" 1989  4        .
10001 "31-May-89" 1989  5        .
end

gen mdate = ym(year, month)
gen fyear = year if month == 6
bysort permno (mdate) : replace fyear = fyear[_n-1] if missing(fyear)
rangestat wanted=bm, int(fyear -1 -1) by(permno)

list, sepby(fyear)

[Andrea Repetto]

Dear Nick,
the code worked out perfectly.
Thank you so much
kind regards,
Andrea