How to add additional observations to panel data?

Question

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
}

BB King · Accepted Answer

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'

How to add additional observations to panel data?

One Answer

Add your own answers!

Ask a Question