Dear Stata community,

I need to reshape/reorganize my data for analyzing the results of a survey experiment with a fractional factorial design. Respondents were asked to rate 6 randomly chosen vignettes that varied along 5 dimensions. The size of the vignette universe was 3 X 3 X 2 X 2 X2 = 72. The vignettes were rated on a 7-point scale, ranging from 1 (very negative) to 7 (very positive).

I copy-pasted the output from the dataex command below. The variables DX1 DX2 DX3 .... DX71 DX 72 represent distinct vignette profiles. Q1 is a string variable showing which vignettes the respondent rated. For example, the first respondent rated vignettes 42, 56, 55, 49, 26, and 39.

I'd like to reshape my data in the long format so that each respondent has 6 entries, one for each vignette he or she rated. In other words, the rows will be respondent-vignette pairs. Next, I'd like to break down the vignettes (DX1 to DX72) into their component dimensions. I know, for instance, that the first 24 vignettes (variables DX1 to DX24) profiled a young person, the next 24 vignettes (DX25 to DX48) profiled a middle-age person, and the last 24 vignettes (DX49 to DX72) profiled an old person. I'd like to create a variable for the age of the fictional person rated by my respondents. Let's say I code age as 1: young, 2: middle-age, 3: old. My first respondent-vignette pair shows a respondent that rated vignette #42, a middle-age person. Thus, he or she should get a score of 2 on age. I need to repeat this procedure for the remaining 4 dimensions as well. Finally, I need to generate a new variable that represents the rating given to the vignette by the respondent.


This is how my data looks like:
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input int ID str20 Q1 byte(DX1 DX2 DX3 DX4 DX5 DX6 DX7 DX8 DX9 DX10 DX11 DX12 DX13 DX14 DX15 DX16)
158 "#,42,56,55,49,26,39," . . . . . . . . . . . . . . . .
165 "#,31,2,24,54,47,33,"  . 2 . . . . . . . . . . . . . .
166 "#,6,28,63,38,40,26,"  . . . . . 6 . . . . . . . . . .
167 "#,25,65,44,54,37,56," . . . . . . . . . . . . . . . .
170 "#,67,61,5,59,28,6,"   . . . . 5 5 . . . . . . . . . .
171 "#,45,2,11,3,32,7,"    . 2 2 . . . 2 . . . 2 . . . . .
172 "#,57,61,60,18,22,59," . . . . . . . . . . . . . . . .
173 "#,58,51,4,28,12,52,"  . . . 7 . . . . . . . 7 . . . .
174 "#,38,29,8,55,50,57,"  . . . . . . . 3 . . . . . . . .
175 "#,50,41,4,34,15,68,"  . . . 5 . . . . . . . . . . 6 .
end

Thank you very much in advance!