Conditional average by group

[Soyoung Han]

Hello,

I have an individual (child) level panel data. In the data set, I have child's ID (CYRB_XRND), mother's ID (MPUBID), the number of children each mother has (num_children), the rank of each child among siblings (rank_child), and educational level (educ).

bysort MPUBID: egen num_children = nvals(CPUBID)

bysort MPUBID (CYRB_XRND) : gen rank_child = CYRB_XRND != CYRB_XRND[_n-1]
by MPUBID : replace rank_child = sum(rank_child)

I want to calculate the average educational level of older siblings for each child every year. Here, I can't use rangestat or asrol by MPUBID*year groups because not only do I need to exclude the value of self educational level, but also younger siblings' (indicated by rank_child). Could someone help me figure out the best way to approach this?

Thank you!

[Clyde Schechter]

I'm pretty sure you can do this with -rangestat-:

gen lower = 1
gen upper = rank_child - 1
rangestat (mean) educ, by(MPUBID) interval(rank_child lower upper) excludeself

Added: In fact, the -excludeself- option isn't really needed here because the child's own rank will never fall between lower (= 1) and upper (= child_rank - 1).

Also added: If this is not what you are looking for, please include some -dataex- output to show an example of your Stata data set, and explain how the results are different from what you want. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Soyoung Han]

Clyde Schechter Thank you so much for your help! This is exactly what I wanted. I just needed to add year into group: by(MPUBID year).