Consolidating multiple-select option variable at the respondent level

[vaishakh nair]

Hi all,

In the below set,
I have data for different respondents(resp_id). The concerned variable for my query is 'accompaniment' which is a multiple-select question in the survey form.

I would like to analyse the data at the respondent id level (resp_id). Corresponding to the resp_id, I want to see the consolidated 'accompaniment' variable.
i.e., I want to have all the possible accompaniment values corresponding to resp_id and generate it as a new variable, say 'consolidated_accompaniment'. The consolidated_accompaniment variable only needs to have one value for each resp_id, example: for resp_id:100109, it(consolidated_accompaniment) should be "1 3 5 12 13". I would like to generate this variable (consolidated_accompaniment) covering all resp_ids in the data. My intention is to then use the mrtab command on consolidated_accompaniment which will give me the accompaniment % at the respondent level.

input float date str6 resp_id byte activity str5 accompaniment
23233 "100109" 1 ""
23233 "100109" 13 ""
23233 "100109" 12 ""
23233 "100109" 3 "1 3 5"
23233 "100109" 9 "12"
23233 "100109" 7 ""
23233 "100109" 2 "13"
23233 "100109" 11 "1"
23233 "100109" 4 ""
23233 "100109" 5 "1"
23233 "100109" 8 ""
23233 "100109" 6 ""
23233 "100109" 10 ""
23211 "100109" 4 "1"
23211 "100109" 9 "1"
23211 "100109" 8 "1"
23211 "100109" 3 "1"
23211 "100109" 6 ""
23211 "100109" 10 ""
23211 "100109" 2 ""
23211 "100109" 5 "1"
23211 "100109" 11 ""
23211 "100109" 7 ""
23211 "100109" 1 "1"
23214 "100109" 2 "1"
23214 "100109" 16 "1"
23214 "100109" 12 "1"
23214 "100109" 13 "1"
23214 "100109" 10 ""
23214 "100109" 8 ""
23214 "100109" 5 ""
23214 "100109" 11 "1"
23214 "100109" 9 "1"
23214 "100109" 3 ""
23214 "100109" 17 ""
23214 "100109" 15 ""
23214 "100109" 1 ""
23214 "100109" 7 ""
23214 "100109" 4 ""
23214 "100109" 14 ""
23214 "100109" 6 ""
23222 "100109" 6 ""
23222 "100109" 7 ""
23222 "100109" 2 ""
23222 "100109" 3 "1"
23222 "100109" 4 ""
23222 "100109" 5 "1"
23222 "100109" 1 ""
23216 "100109" 6 ""
23216 "100109" 4 ""
23216 "100109" 3 ""
23216 "100109" 10 ""
23216 "100109" 11 "1"
23216 "100109" 1 ""
23216 "100109" 13 ""
23216 "100109" 14 "1"
23216 "100109" 12 "1"
23216 "100109" 8 "1"
23216 "100109" 2 ""
23216 "100109" 9 ""
23216 "100109" 5 ""
23216 "100109" 7 ""
23224 "100109" 9 "1"
23224 "100109" 1 ""
23224 "100109" 7 ""
23224 "100109" 8 ""
23224 "100109" 11 ""
23224 "100109" 10 "1"
23224 "100109" 3 "1"
23224 "100109" 4 ""
23224 "100109" 2 ""
23224 "100109" 6 ""
23224 "100109" 5 "1"
23221 "100109" 8 "1"
23221 "100109" 6 ""
23221 "100109" 2 ""
23221 "100109" 1 ""
23221 "100109" 5 ""
23221 "100109" 9 "1"
23221 "100109" 4 ""
23221 "100109" 3 ""
23221 "100109" 7 ""
23233 "100212" 9 ""
23233 "100212" 5 "1"
23233 "100212" 8 "1"
23233 "100212" 4 ""
23233 "100212" 11 ""
23233 "100212" 10 "1"
23233 "100212" 6 ""
23233 "100212" 1 ""
23233 "100212" 7 ""
23233 "100212" 2 ""
23233 "100212" 3 "1"
23222 "100212" 10 "1"
23222 "100212" 3 "1"
23222 "100212" 8 "1"
23222 "100212" 9 ""
23222 "100212" 12 ""
23222 "100212" 6 ""
23222 "100212" 5 ""
end
format %d date
[/CODE]

[Clyde Schechter]

by resp_id (accompaniment date), sort: gen consolidated_accompaniment = accompaniment ///
    if _n == 1
by resp_id (accompaniment date): replace consolidated_accompaniment = ///
    consolidated_accompaniment[_n-1] + ///
    cond(inlist(accompaniment, "", accompaniment[_n-1]), "", " " + accompaniment) if _n > 1
by resp_id (accompaniment date): replace consolidated_accompaniment ///
    = trim(itrim(consolidated_accompaniment[_N]))

[vaishakh nair]

by resp_id (accompaniment date), sort: gen consolidated_accompaniment = accompaniment ///
if _n == 1
by resp_id (accompaniment date): replace consolidated_accompaniment = ///
consolidated_accompaniment[_n-1] + ///
cond(inlist(accompaniment, "", accompaniment[_n-1]), "", " " + accompaniment) if _n > 1
by resp_id (accompaniment date): replace consolidated_accompaniment ///
= trim(itrim(consolidated_accompaniment[_N]))

Thanks a lot Clyde!
I have a small issue still. For resp_id 100418, the newly generated 'consolidated_accompaniment' list shows " 1 2 2 3 5 2 5 3 4 4 6 ". What I would ideally want is just unique values, i.e. " 1 2 3 4 5 6 ".
Is it possible to achieve this?
Updated dataex below for the above query:-

input float date str6 resp_id byte activity str5 accompaniment
23211 "100418" 1 ""
23211 "100418" 7 ""
23211 "100418" 11 ""
23211 "100418" 8 ""
23211 "100418" 5 ""
23214 "100418" 8 ""
23214 "100418" 7 ""
23214 "100418" 4 ""
23214 "100418" 12 ""
23214 "100418" 6 ""
23214 "100418" 1 ""
23214 "100418" 10 ""
23216 "100418" 4 ""
23216 "100418" 9 ""
23216 "100418" 1 ""
23216 "100418" 7 ""
23216 "100418" 11 ""
23216 "100418" 6 ""
23216 "100418" 13 ""
23221 "100418" 7 ""
23221 "100418" 6 ""
23221 "100418" 4 ""
23221 "100418" 1 ""
23221 "100418" 5 ""
23221 "100418" 8 ""
23222 "100418" 10 ""
23222 "100418" 12 ""
23222 "100418" 4 ""
23222 "100418" 7 ""
23222 "100418" 6 ""
23222 "100418" 1 ""
23224 "100418" 10 ""
23224 "100418" 4 ""
23224 "100418" 8 ""
23224 "100418" 12 ""
23224 "100418" 1 ""
23224 "100418" 7 ""
23224 "100418" 6 ""
23233 "100418" 4 ""
23233 "100418" 5 ""
23233 "100418" 6 ""
23233 "100418" 7 ""
23233 "100418" 1 ""
23233 "100418" 10 ""
23211 "100418" 6 "1"
23211 "100418" 2 "1"
23211 "100418" 9 "1"
23211 "100418" 10 "1"
23214 "100418" 2 "1"
23214 "100418" 11 "1"
23214 "100418" 13 "1"
23214 "100418" 9 "1"
23214 "100418" 5 "1"
23216 "100418" 12 "1"
23216 "100418" 14 "1"
23216 "100418" 2 "1"
23216 "100418" 5 "1"
23221 "100418" 9 "1"
23221 "100418" 3 "1"
23221 "100418" 2 "1"
23222 "100418" 2 "1"
23222 "100418" 11 "1"
23222 "100418" 5 "1"
23222 "100418" 13 "1"
23222 "100418" 3 "1"
23222 "100418" 9 "1"
23222 "100418" 8 "1"
23224 "100418" 3 "1"
23224 "100418" 5 "1"
23224 "100418" 9 "1"
23224 "100418" 13 "1"
23224 "100418" 11 "1"
23233 "100418" 3 "1"
23233 "100418" 8 "1"
23233 "100418" 9 "1"
23233 "100418" 2 "1"
23216 "100418" 8 "2"
23216 "100418" 10 "2"
23216 "100418" 3 "2 3 5"
23211 "100418" 3 "2 5"
23211 "100418" 4 "3"
23224 "100418" 2 "4"
23214 "100418" 3 "4 6"
23233 "100212" 9 ""
23233 "100212" 5 "1"
23233 "100212" 8 "1"
23233 "100212" 4 ""
23233 "100212" 11 ""
23233 "100212" 10 "1"
23233 "100212" 6 ""
23233 "100212" 1 ""
23233 "100212" 7 ""
23233 "100212" 2 ""
23233 "100212" 3 "1"
23222 "100212" 10 "1"
23222 "100212" 3 "1"
23222 "100212" 8 "1"
23222 "100212" 9 ""
23222 "100212" 12 ""
23222 "100212" 6 ""
23222 "100212" 5 ""
end
format %d date
[/CODE]

[Clyde Schechter]

Ah, sorry. I see I neglected to take into account that each individual value of accompaniment could contain more than one number. The following code corrects that:

split accompaniment
drop accompaniment
gen `c(obs_t)' obs_no = _n
reshape long accompaniment, i(obs_no)
by resp_id (accompaniment date), sort: gen consolidated_accompaniment = accompaniment ///
    if _n == 1
by resp_id (accompaniment date): replace consolidated_accompaniment = ///
    consolidated_accompaniment[_n-1] + ///
    cond(inlist(accompaniment, "", accompaniment[_n-1]), "", " " + accompaniment) if _n > 1
by resp_id (accompaniment date): replace consolidated_accompaniment ///
    = trim(itrim(consolidated_accompaniment[_N]))
reshape wide
egen accompaniment = concat(accompaniment?*), punct(" ")
drop accompaniment?*

Notice that the code begins by splitting up the accompaniment variable into its individual constituents. The final two lines of code restore the original accompaniment variable by putting the constituents back together again. But if you won't need that later on, and can make do with the separate constituent variables, you can skip those last two lines.

[vaishakh nair]

The above code is working well, Clyde. Thanks again!