Hello,

I am currently working on an issue which I think is straight forward but somehow I am not able to solve this issue.

I want to calculate a new variable by dividing variable1(t-1)/variable(t).

In my specific case.

For June 2017 I want a new variable b_m which will be calculated by: book month 6 year 2016 divided by Mcap month 12 year 2016 in other word (book, 6, 2016/Mcap, 12, 2016).

I tried following code. First buidling two new variables and divide them

Code:
bysort ISIN year month: gen book6 = month == 6
bysort ISIN year month: gen market12 = month == 12
keep if book6 == 1 | market12 == 1
bysort ISIN year: gen bm = (book/Mcap)/1000
There are certain issues:
Nr.1 i do not want to loose all my prices observations for month 1-5 and 7-11
Nr. 2 it only calculates my b_m for the current year. As I said above I try to calculate my values for the next year using the data from the current year.
Nr. 3 besides that the code is wrong, it is also probably not very efficient written

To give an example what number b_m 2017/6 should be:

(66472421/25102.75)/1000 = 2.6481

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 ISIN double prices long book float(Mcap dates year month)
"DE0005140008"  426.15 66472421  19152.57 20635 2016  6
"DE0005140008"   413.2 66909671 18570.518 20664 2016  7
"DE0005140008"  450.99 66637278   20268.4 20697 2016  8
"DE0005140008"  403.72 67239335 18144.322 20727 2016  9
"DE0005140008"  441.38 65589113  19836.69 20758 2016 10
"DE0005140008"  483.45 63472048  21727.67 20788 2016 11
"DE0005140008"  558.54 63109914  25102.75 20818 2016 12
"DE0005140008"  608.94 68272614 27367.516 20850 2017  1
"DE0005140008"  605.87 67128207  27229.59 20878 2017  2
"DE0005140008"  590.81 67565169  35534.01 20909 2017  3
"DE0005140008"  620.41 68793274  37315.57 20937 2017  4
"DE0005140008"  614.17 71038011  36534.33 20970 2017  5
"DE0005140008"  617.79 72052622 36749.273 21000 2017  6
"DE0005140008"  620.35 74482161 36902.215 21031 2017  7
"DE0005140008"  557.97 75106311 33190.293 21062 2017  8
"DE0005140008"  598.08 74683923 35577.414 21091 2017  9
"DE0005140008"  564.75 73594151 33593.313 21123 2017 10
"DE0005140008"  658.62 75326264  39177.73 21153 2017 11
"DE0005140008"  661.47 75857338  39347.21 21182 2017 12
"DE0005140008"  642.07 77854180 38193.945 21215 2018  1
"DE0005140008"  559.12 76222207 33258.496 21243 2018  2
"DE0005140008"   483.7 76857271   28773.6 21273 2018  3
"DE0005140008"  476.71 75505952  28356.11 21304 2018  4
end
format %tdCCYYNNDD dates
Thanks