topic Many-to-many merge of 2 datasets with specific conditions in SAS Procedures

Many-to-many merge of 2 datasets with specific conditions

deleted_user — Fri, 18 Feb 2011 01:20:04 GMT

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;

Re: Many-to-many merge of 2 datasets with specific conditions

DBailey — Fri, 18 Feb 2011 03:54:26 GMT

how about this:

proc sql;
create table dataAB as
select dataA.*, dataB.*
from dataA, dataB
where
dataA.ID=dataB.id
and dataA.admitdate between dataB.entrydate and dataB.exitdate;
quit;

Re: Many-to-many merge of 2 datasets with specific conditions

deleted_user — Fri, 18 Feb 2011 05:32:34 GMT

Thanks DBailey.

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.