this is my sample dataset

enter image description here

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
  • 1
    $\begingroup$ Can you show us what did you tried so far? $\endgroup$ – 1muflon1 Oct 21 '20 at 17:03
  • $\begingroup$ Please see my edit $\endgroup$ – 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
keep if month==8 | month==9
collapse value , by(year category id)
gen month=10
save `month10' , replace
append using `month10'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.