how to create a staff loop? - reshape & nested data

[Susanne Ulrich]

Hi everybody,
I have a question on reshaping my data for creating a staff loop (worker d1,d2,d3 with id=idpid) with characteristics (e.g. qualification t1, working hours t2, health t3) for each person in several communities (id=id).

What I have is nested data with all information on the workers in one row for each community (wide format).

id	d1t1	d1t2	d1t3	d2t1	d2t2	d2t3	d3t1	d3t2	d3t3
1	4	5	6	5	6	5	6	7	8
2	3	4	5	4	5	4	3	4	5

What I want is data with all information on each worker in a seperate row including the community information as column (long format).

id	idpid	dt1	dt2	dt3
1	1	4	5	6
1	2	5	6	7
1	3	6	5	8
2	1	3	4	3
2	2	4	5	4
2	3	5	4	5

What I did is reshaping my data as described here (https://stats.idre.ucla.edu/stata/fa...-data-to-long/), but I can't handle my nested data structure properly. I got all the information on different characteristics of the workers in one variable and unable to reshape it back.
Can anyone help me figure out what to do next?

Here is the example code:

clear
input d1t1 d1t2 d1t3 d2t1 d2t2 d2t3 d3t1 d3t2 d3t3
4 5 6 5 6 5 6 7 8 2
3 4 5 4 5 4 3 4 5 3
end

generate id = _n

foreach v of varlist d1t1 d1t2 d1t3 d2t1 d2t2 d2t3 d3t1 d3t2 d3t3 {
rename `v' y`i'
local i = `i' + 1
}
rename y y0
reshape long y, i(id) j(seq)
clist if i==1
recode seq (0 3 6=1) (1 4 7=2) (2 5 8=3), gen(pid)
clist if i==1

Thank you in advance!
Susanne

[Ken Chui]

clear
input d1t1 d1t2 d1t3 d2t1 d2t2 d2t3 d3t1 d3t2 d3t3
4 5 6 5 6 5 6 7 8
3 4 5 4 5 4 3 4 5
end

gen id = _n

reshape long d, i(id) j(temp1, string)

gen idpid    = real(substr(temp1, 3, 1))
gen temp_seq = real(substr(temp1, 1, 1))
drop temp1

reshape wide d, i(id idpid) j(temp_seq)
rename d* dt*