Dear all,

I have a database comprised of an id variable (siteid), two variables that indicate the limits of a period of interest (end1 and when2, which changes according to different observations) and weekly information for each observation (w13 - w29). The example below is a simplification of the dataset, but it does show the structure of the data and the kind of problem I have in hands:

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input int siteid byte(end1 when2) str1(w13 w14 w15 w16 w17 w18 w19 w20 w21 w22 w23 w24 w25 w26 w27 w28 w29)
1 16 23 ""  "D" "" ""  ""  "" "A" ""  ""  "" "D" ""  "" ""  "" ""  "" 
2 13 23 ""  ""  "" "A" ""  "" ""  ""  ""  "" "D" ""  "" ""  "" ""  "" 
3 17 29 "D" ""  "" ""  ""  "" ""  "A" ""  "" ""  ""  "" "B" "" ""  "D"
4 14 21 ""  ""  "" "B" ""  "" ""  "C" "D" "" ""  ""  "" ""  "" ""  "" 
5 20 28 ""  "B" "" ""  "D" "" ""  ""  "B" "" ""  "A" "" ""  "" "D" "" 
end
What I would like to do is for each observation (siteid) to create a new variable (test) that concatenates the values for intervals of w specified in end1 and when2. For example, for siteid==1, since end1==16 and when2==23, I would like to concatenate the values for w17 (end1+1) to w22 (when2-1). For siteid==1, test should be equal to "A" (I'm not really interested in the missing values for weeks in which no information is given). However, in siteid==4, since end1==14 and when2==21, test should be equal to "BC" (the non-missing values of w15 to w20).

The full dataset has 517 week variables and up to 6 periods of interest for each observation (end1 to when2, end2 to when3, end3 to when4 and so on) for a total of ~100 observations. I would like to avoid doing the task above manually, since the likelihood of errors would be too great.

The logic of the problem seems to be complicated, since I would like to use values of variables to select variables I want to concatenate. I considered creating a coding using two forvalues commands (one that would specify the end1 value and other the when2 value) paired with two while commands to restrict the extent of variables I would concatenate, as in the code below (which does not work):

Code:
forvalues i = 13-29}
    forvalues j = 13-29{
        while `i' > end1{
            while `j' < when2{
                egen test = concat(w`i'- w`j')
            }
        }
    }
}
Does anyone have any input on how I can solve this issue? Any help is appreciated.

Sorry for the long post.

Best;