Hi all,

I have data at the individual level which looks like the following:

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input float id str6 quarter float(Yes score) str1 activity
1 "2000q1" 1 20 "x"
1 "2000q1" 0  0 "y"
1 "2000q1" 0  0 "z"
1 "2000q1" 0  0 "a"
1 "2000q2" 0  0 "x"
1 "2000q2" 0  0 "y"
1 "2000q2" 1 10 "z"
1 "2000q2" 0  0 "a"
1 "2000q3" 0  0 "x"
1 "2000q3" 0  0 "y"
1 "2000q3" 1 15 "z"
1 "2000q3" 0  0 "a"
1 "2000q4" 0  0 "x"
1 "2000q4" 0  0 "y"
1 "2000q4" 0  0 "z"
1 "2000q4" 1  5 "a"
end

where each individual (id) participated in one or more of the activities a, x, y, or/and z at any time (quarter) and obtained a score for participation. I was trying to transform the data to be at the activity level, as below :

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id str2 quarter byte(x y z a x_score y_score z_score a_score)
1 "q1" 1 0 0 0 20 0  0 0
1 "q2" 0 0 1 0  0 0 10 0
1 "q3" 0 0 1 0  0 0 15 0
1 "q4" 0 0 0 1  0 0  0 5
end

Any idea how to do this?