Hi, the problem I am currently trying to solve requires some background, so please bear with me:
I am a medical student working on a retrospective study of some medical records, and one piece of data that I am collecting is time spent in the operating room for a specific operation. All of the other information that I am collecting comes from "program X" (our hospital's medical record software) where I will be manually reading through notes to find the information that I need. However, this one piece of information is stored on "program Y" (medical record software specific to the operating rooms). Unfortunately, I do not have access to "program Y," so I talked to our medical records people, and their solution was to have the program dump the relevant information into an Excel table that they were able to give me. The problem is that what they were able to give me was a list of every procedure done at our hospital for the time span that I indicated, and that added up to just under 76,000 entries. Only 2,400 of those entries are ones that I need. Now I need to correlate the data.
I currently have 2 Excel tables: one table (Table 1) with the MRN (medical record number) and DC_DATE (discharge date from the hospital), and another table (Table 2) with MRN, OP_DATE (date of the operation), and OP_TIME (time spent in operating room). My end goal is a table with MRN, DC_DATE, OP_DATE, and OP_TIME.
Problem 1: I can't just link the two via MRN because the patient could have had more than one operation within the specified time period, so they could have more than one entry in Table 2. In order to guarantee that I am matching the correct entries, I also need to link DC_DATE from Table 1 with OP_DATE from Table 2.
Problem 2: DC_DATE and OP_DATE may not be the same day because the patient may have had the operation and stayed in the hospital for another couple days.
Question: Is it possible to link two tables with approximate matches? For example, can I link MRN and DC_DATE from Table 1 to the MRN and closest OP_DATE (relative to DC_DATE) from table 2? Alternatively, can I set a parameter that links the DC_DATE with an OP_DATE that falls within a range, e.g. within 7 days prior to the DC_DATE?
I really appreciate your help! Also, if anyone has an alternative method that might work, I'm all ears. I just don't want to spend countless hours going through and manually looking up these numbers when I'm already going to have to be looking up a ton of other information from the actual patient charts.