Merge yearly to period panel data

[Henry Strawforrd]

I have two datasets. One is a panel dataset with year as time and id as unit dimension.

input year id x
1 1 2
2 1 3
3 1 4
4 1 5
1 2 3
2 2 4
3 2 2
4 2 5
end

The second one contains the same id but a time period

input str10 period int id
"1-2" 1
"2-4" 1
"1-3" 1
"2" 1
"1" 2
"2-4" 2
"1-3" 2
"4" 2
end

I want to merge these datasets so that I have the average value of x over that period in the second dataset. How can I do that?

[Øyvind Snilsberg]

use second, clear
gen year1 = real(substr(period,1,1))
gen year2 = real(substr(period,-1,1))
gen year3 = year2 - 1 if year2 - 1 > 1
bys id: gen n = _n
reshape long year, i(id n)
merge m:1 year id using first
collapse x, by(id period)

[Henry Strawforrd]

Thanks, but in reality I have much more than three year intervals. This was just a coincidence in the toy example.

[Øyvind Snilsberg]

I see, how about,

use second,clear
gen p1 = real(substr(period,1,1))
gen p2 = real(substr(period,-1,1))
su p2
expand r(max)
bysort id period: gen year = _n
keep if inrange(year,p1,p2)
merge m:1 id year using first
collapse x, by(id period)

[Henry Strawforrd]

I think this fails because the periods have different lengths between id's. Sorry

[Henry Strawforrd]

I have adapted the solution poster under #6 here
https://www.statalist.org/forums/for...ls-in-the-rows

To do part of the period reshape and then the previous solution posted here works.