I would like to carry out a many-to-many merge of 2 datasets with specific conditions attached. Each of the 2 datasets has a list of patients with a unique 'ID'. Datasets can only be matched on this 'ID' variable.
Dataset A (called 'dataA') has an admission date (called 'admitdate') for each patient but a patient may have multiple entries with different admission dates (ie. the same patient may appear more than once with different admit date).
Dataset B (called 'dataB') has two dates specifying an entry (called 'entrydate') and exit (called 'exitdate') time from a facility for a patient. But again, the patient may have more than one set of dates (ie. the same patient may have resided in 2 different facilities over the study period).
I would like to merge 'dataA' and 'dataB' but retain only records that have a patient in 'dataB'.
I need the data to be merged by ID but only if the 'admitdate' falls within the 'entrydate' and 'exitdate' time period.
I have tried the following code but do not get the results I want.
create table dataAB as
select dataA.*, dataB.*
from dataA, dataB
Can I add a 'where' statement to this?
I have also tried:
merge dataB (in=i1) dataA (in=i2);
if i1=1 then do;
if i1=1 and admitdate >= entrydate and admitdate <= exitdate then output dataAB;
Now I'd like to sum up the number of admits and also the length of stay for each patient.
The problem is now I have some patients with multiple 'admitdates' within a single time period (there is a different record/observation for each 'admitdate'). If I sum up the length of stay for these patients I will be counting the 'entrydate' to 'exitdate' time more than once.
I am wondering if I can sum up the number of 'admitdates' for a patient within a certain time period (entrydate and exitdate) to create a single record for that patient for a particular time period.
For example, I now have a patient with 3 different 'admitdate's for the time period 'entrydate' to 'exitdate'. If each 'admitdate' is a single admission, how can I sum the number of admissions (in this case the sum would equal 3) for that patient within that period. This patient might also have other admissions during different time period.