Pulling a Value from an Observation into Another Observation based on Specific Criteria

[Shannon Green]

Hi all,

Hoping to get some help on an issue. I am running on Stata SE 18.0.

I have a dataset that includes variables such as:

case | partyid | relatedcase | filedate
D12 987 M67 4/12/22
D13 987 6/21/23
M67 987 1/30/22

Based on the above example, I need to pull filedate (1/30/22) from case M67 into case D12 (with relatedcase M67). I am hitting a wall in how to do that. The dataset has over 1.8 million observations.

So ideally, it would look like this afterwards:

case | partyid | relatedcase | filedate | originalfiledate
D12 987 M67 4/12/22 1/30/22
D13 987 6/21/23
M67 987 1/30/22

Additionally, I would like to then drop all cases that start with M after pulling their filedates into their related cases that start with D.

Thanks!

[Mike Lacy]

Please take another look at the FAQ for new members (tab at the top of the StataList screen), and read again about -dataex-. Use that command to prepare a data example and post it here, preferably one with several more observations, including ones that might exemplify any odd situations you might have. Doing that will substantially increase your chances of getting help quickly.

One confusing feature of your current example is that some of your observations have 4 variables, and others only 3. Presumably you meant that some observations were missing values for -relatedcase-, but you left us to guess about that. Also, I would guess that you intended to link together cases where the "case" string matches the "relatedcase" string, but having some explicit instructions there would be helpful. It would also be important to know whether case can be linked to only one other case, or whether (say) case "M67" might match with several other cases. These things may be obvious to you, but not necessarily to others. I *think* your rule is "For all observations that have a valid value for "related case," find another observation whose "case" value matches that "related case," and spread the "originafiledate" value to observations that are not missing a value for "related case."