transform/reshape data

[Galal Ali]

Hi all,

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

* 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 :

* 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?

[Nick Cox]

* 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

reshape wide Yes score, i(id quarter) j(activity) string 
rename (Yes*) (*)
rename (score*) (*_score)
list 

     +----------------------------------------------------------------------+
     | id   quarter   a   a_score   x   x_score   y   y_score   z   z_score |
     |----------------------------------------------------------------------|
  1. |  1    2000q1   0         0   1        20   0         0   0         0 |
  2. |  1    2000q2   0         0   0         0   0         0   1        10 |
  3. |  1    2000q3   0         0   0         0   0         0   1        15 |
  4. |  1    2000q4   1         5   0         0   0         0   0         0 |
     +----------------------------------------------------------------------+

[Galal Ali]

Many thanks Nick Cox