this is my sample dataset

How can I add for each id a new observation for 2020 month 10 which would be the average of 2020 month 8 and 2020 month 9?

I have tried the following, however, it almost works but it does not assign category name to the corresponding id value and the average is not taken correctly

egen year_m_id=group(year month) /* 127 & 128 correspond to 2020 month 8,9 */
global id=r(levels)
foreach id of global id{
generate tmp=category if id==id'
local 2020_9=_N+1
set obs 2020_9'
local 2020_9=_N
replace id =id' if _n==2020_9'
sum value if year_m_id===127|year_m_id==128 & id == id'
replace value = r(mean) if _n==2020_9' & id == id'
replace year = 2020 if _n==2020_9'
replace month = 2020 if _n==2020_9'
replace category = tmp if _n==2020_9' & category=="" & _n==2020_9'
drop tmp
}

• Can you show us what did you tried so far? – 1muflon1 Oct 21 '20 at 17:03
• Please see my edit – user30936 Oct 21 '20 at 19:37

Not sure if this is on-topic, but I'll give a simple solution. It's perhaps not the most efficient, but should be easy enough to follow.

Assuming you also want to keep "category" and not just "id", how about something like the following:

use sample.dta, clear
tempfile month10
preserve
keep if month==8 | month==9
collapse value , by(year category id)
gen month=10
save month10' , replace
restore
append using month10'
`