Help using Base SAS procedures

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

Reply
N/A
Posts: 0

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

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;
Super Contributor
Posts: 578

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

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;
N/A
Posts: 0

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

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.
Ask a Question
Discussion stats
  • 2 replies
  • 148 views
  • 0 likes
  • 2 in conversation