Generating serial number

[JalalShah]

I have monthly serial in the format given below. It is based on the daily data, so that in each month every daily observation have same identification value for example 1 in month 1 and 2 in month 2 and so on. Now I want to generate another serial which could gave the same identification value to two months in a rolling manner. For example, it should give value 1 to observations in month 1 and in month 2, and should give value 2 to observations in month 2 and in month 3 and so on as shown below:

Mon_serial	New_ser1	New_ser2
1	1
1	1
1	1
1	1
2	1	2
2	1	2
2	1	2
2	1	2
2	1	2
2	1	2
2	1	2
2	1	2
2	1	2
3		2
3		2
3		2
3		2
3		2

Similarly, I need to generate serial for 3, 4 months till 36 months. If anyone could give me the code for generating these serial. I would be grateful.

[Maarten Buis]

// create some example data
clear
set obs 15
gen mon = ceil(_n/3)

// to illustrate potential problem
// add a gap
replace mon = . if mon == 4

// look at that example data
list, sepby(mon)


//==================  sollution 1:
// find the values of mon
levelsof mon
local levs `r(levels)'
local k : word count `levs'
tokenize `levs'

forvalues i = 1/`=`k'-1' {
    gen new1_`i' = inlist(mon,``i'', ``=`i'+1'')*``i'' if mon < .
}

//================== solution 2:
sum mon, meanonly
forvalues i = `r(min)'/`=`r(max)'-1' {
    gen new2_`i' = inlist(mon,`i', `=`i'+1')*`i' if mon < .
}

// see the results:
list, sepby(mon)

[Abraham Wolde-Tsadick]

Here is another way using time-series operator

bys Mon_serial: keep if _n == _N
tsset Mon_serial
levelsof Mon_serial, local(Mon_serial)
foreach i of local Mon_serial {
gen New_ser`i' = Mon_serial if Mon_serial == `i'
replace New_ser`i' = L1.Mon_serial  if Mon_serial == `i' + 1
}
save ser.dta, replace use master.dta, clear
merge m:1 Mon_serial using ser.dta

Abraham

[JalalShah]

Thank you Dear Maarten Buis and Mr. Abraham for your response. Your commands seem useful, however my supervisor, Dr. Attaullah Shah have also developed one for this which is given below:

*Data Preperation


gen year=year(date)
gen month=month(date)


*one month serial number
sum year
scalar min = r(min)
scalar max = r(max)
gen mon_ser = (year - min)*12 + month
sum mon_ser
sca minm = r(min)
replace mon_ser = mon_ser - minm + 1


*One month serial number generation completed

---

We are ready to generate monthly geometric cumulative returns from daily data. Suppose our returns are recorded in

---

*empty variable that will hole cumulative returns
gen SCAR=.
*period identifier
gen Period = .
qui sum mon_ser
loc pmax = r(max)
forval i = 1(1) `pmax' {
qui{
egen scarm=sum( ln(rm+1)) if mon_ser==`i'
gen carm = exp(scarm) if mon_ser==`i'
qui sum carm
replace SCAR=r(mean)-1 in `i'
replace Period = `i' in `i'
drop scarm carm
}
}

---

What if we want the cumulative returns for more than one month. For example, if we want cumulative returns for 36 months, the code would be

---

loc F "(1+SCAR)"
sum Period
loc smax=r(max)
forval i = 1 / 36 {
loc F "`F' * (1+L`i'.SCAR)"
gen rm`=`i'+1'=`F' - 1


}




The code is also available through this link: http://www.opendoors.pk/STATA/cumula...o-monthly-data