Need help with reshaping data

[Ribka Berhanu]

Hi - I need assistance with reshaping data.

I have a dataset that looks like this where data from redcap is imported into separate forms and then the variables for each form are listed horizontally. I am struggling with the reshape command because the data is both long and wide at the same time.

I would like to have a single row per study id number with all the data in wide form.

This is what the data currently looks like:

studyID	form	form_a_var_1	form_a_var_2	form_a_var_3	form_b_var_1	form_b_var_2	form_b_var_3
1	a	x	x	x
1	b				x	x	x
2	a	x	x	x
2	b				x	x	x

And this is what I would like it to look like:

studyID	form	form_a_var_1	form_a_var_2	form_a_var_3	form_b_var_1	form_b_var_2	form_b_var_3
1	a	x	x	x	x	x	x
2	a	x	x	x	x	x	x

Thanks in advance for your help!

Ribka

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte studyid str1(form form_a_var_1 form_a_var_2 form_a_var_3 form_b_var_1 form_b_var_2 form_b_var_3)
1 "a" "x" "x" "x" ""  ""  "" 
1 "b" ""  ""  ""  "x" "x" "x"
2 "a" "x" "x" "x" ""  ""  "" 
2 "b" ""  ""  ""  "x" "x" "x"
end

collapse (firstnm) form*, by(studyid)

drop form

I added the final command to drop the variable form because it is both uninformative and actually misleading in the context of the final data.

By the way, this kind of data rearrangement is different from what the Stata -reshape- command does. It is likely that for further analysis you will need to actually reshape this data into a partially or fully long layout. But that's a different issue.

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.



When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Ribka Berhanu]

Thank you so much! I had to tweak the solution slightly but it worked perfectly. This was the code I used:
collapse (firstnm)form_a_var_1 (firstnm) form_a_var_2 (firstnm) form_a_var_3 (firstnm) form_b_var_1 (firstnm) form_b_var_2 (firstnm) form_b_var_3, by(studyid) Will definitely use dataex in the future.