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.
proc sql;
create table dataAB as
select dataA.*, dataB.*
from dataA, dataB
where dataA.ID=dataB.ID;
quit;
Can I add a 'where' statement to this?
I have also tried:
data dataAB;
merge dataB (in=i1) dataA (in=i2);
by ID;
if i1=1 then do;
if i1=1 and admitdate >= entrydate and admitdate <= exitdate then output dataAB;
end;
run;