Hi everyone. I ran into a problem merging that I thought I should post here because it could be useful for someone down the road if we figure this out.
Does anyone know if there is a way for stata to generate unique IDs based off of another variable BUT also generate a new ID if it encounters a duplicate in that row.
I am trying to merge two datasets, that have different identifying IDs so I cant do 1:1 merge. However, they share the same medical record number. But the participant show up multiple times and merge with many to many attaches outcomes for each record number it encounters even if that outcome did not occur on that day. The extra layer of difficulty is that the overlap isn't 1:1 (i.e. I have 200 or so observations in one file and 1000+ in the other. The overlap is with the record number. is overlap of MRNs).
A potential solution I see but I dont know how to do is to generate a new ID off the record number (i.e. Record # + 111) but when it encounters a duplicate it gives the second observation 112,113...) but it would need to know to then not return to its count (i.e. makes ID 28 instead of 26.
Ex.)
ID Record No
23 12
24 13
25 14
26 14
27 14
28 15
Maybe when it encounters a duplicate it has a different formula like multiply or something. I don't know. I'm reaching here.
Goal of above is to generate new ID's that would be similar across databases so I can merge 1:1.
Any thoughts? Maybe I'm making this harder than it needs to be. My brain hurts X_x
Thanks!