Hi Mariya,
I imagine there are a number of different creative solutions to your issue of analyzing many:many data. Here’s one idea.
If I understand correctly, you have data in multiple files that are in a long format, meaning each ID has data on multiple rows. I would try converting the data in each file into a wide format (where each ID only has one row), using Stata’s reshape command. Once each of your datasets is in a similar format with one observation per row you should be able to merge the datasets into a single dataset matching on ID.
For instance, made up data in the long format might look like this:
id obs icd
1 1 567980
1 2 465879
1 3 457687
2 1 124356
2 2 549687
2 3 609790
3 1 164098
3 2 860980
I would enter the command: reshape wide var1 var2, i(i) j(j)
So for my example, I would type:
reshape wide icd, i(id) j(obs)
Note that you have to specify variable names in the parentheses after i and after j. In this example, Stata retains the variable “id” as the unique identifier and uses the variable “obs” to create suffixes for the wide form of your new icd variables.
Stata will convert the data to look like this:
id icd1 icd2 icd3
1 567980 465879 457687
2 124356 549687 609790
3 164098 860980 .
Note that in the long data, id 3 only had two observations. So in the wide dataset, it has a missing value for icd3
In the data that you’re working with, it sounds like you definitely have id as a variable, but you may not have a j variable (like “obs” in the example above). Luckily, Stata has relatively simple commands that allow you to create a variable like this that can number consecutive observations within a single case. First sort by your id variable, then generate a new variable based on “_n” which is Stata’s notation for observation number.
sort idvar
by idvar: generate newvar = _n
You can then use your newvar as your j variable in a reshape command. Save it as a new dataset. Convert your other dataset into a wide format and save that. Then merge the two by matching ID. I’m not going into detail about the merge command because it sounds like you’ve already been using it.
If you would like more info about reshape, there’s a handout on this topic as one of the additional resources under lecture 3.
Hope that helps! Let us know if additional questions come up.
Maria
