Many to Many Merge - Exclude Based on Date Range

djbateman — Mon, 13 Apr 2015 14:13:54 GMT

I have 2 tables with multiple dates per subject. If the date in TABLE2 occurs within 28 days prior to the date in TABLE1, then the date in TABLE1 cannot be used. My original plan was to transpose TABLE2 by SUBJID so that there is a column for each date and then loop through each SUBJID in TABLE1 with each column in the transposed TABLE2 to see if the TABLE1 date would fall within the exclusion range with any column of TABLE2. I can't imagine that that is the most efficient method. Does anyone have any better ideas?

Here is a sample of what I have:

TABLE1

SUBJID	CA125DT
0001-3314	24-Sep-12
0001-3314	22-Oct-12
0001-3314	5-Nov-12
0001-3314	19-Nov-12
0001-3314	17-Dec-12
0001-3314	14-Jan-13
0001-3314	11-Feb-13
0001-3314	15-Feb-13
0008-4146	15-Oct-12
0014-4387	27-Dec-13
0014-4387	10-Feb-14
0014-4387	21-Mar-14
0014-4387	23-May-14
0020-0020	1-Jul-13
0020-0020	26-Aug-13
0020-0020	3-Sep-13
0020-0093	3-Mar-14
0020-0093	31-Mar-14
0020-0093	14-Apr-14
0020-0093	12-May-14
0020-0093	10-Jun-14
0020-4142	17-Sep-11

TABLE2

SUBJID	STDT
0001-3314	19-Jul-12
0001-3314	31-Aug-12
0001-3314	24-Oct-12
0001-3314	19-Nov-12
0001-3314	21-Dec-12
0001-3314	18-Jan-13
0001-3314	22-Feb-13
0001-3314	9-Mar-13
0008-4146	21-Aug-12
0008-4146	31-Aug-12
0008-4146	12-Sep-12
0008-4146	21-Sep-12
0008-4146	27-Sep-12
0008-4146	9-Oct-12
0008-4146	19-Oct-12
0008-4146	26-Oct-12
0008-4146	8-Nov-12
0014-4387	8-Nov-13
0014-4387	26-Nov-13
0014-4387	4-Dec-13
0020-0020	2-Jul-13
0020-0020	29-Jul-13
0020-0020	5-Sep-13
0020-0093	28-May-14
0020-4142	13-Sep-11

If you were to go line by line, you should see that the following dates are usable from TABLE1:

SUBJID	CA125DT
0001-3314	22-Oct-12
0014-4387	21-Mar-14
0014-4387	23-May-14
0020-0020	1-Jul-13
0020-0020	3-Sep-13
0020-0093	3-Mar-14
0020-0093	31-Mar-14
0020-0093	14-Apr-14
0020-0093	12-May-14

Re: Many to Many Merge - Exclude Based on Date Range

gergely_batho — Mon, 13 Apr 2015 14:25:56 GMT

Do an SQL join with the following join condition:

SUBJID=SUBJID and STDT between CA125DT and intnx('day',CA125DT,-28)

Re: Many to Many Merge - Exclude Based on Date Range

RW9 — Mon, 13 Apr 2015 14:30:01 GMT

You should be able to do it with SQL pretty easily, no need to transpose. I am not following your logic well though, why only one record for 0001-3314? An example:

proc SQL;

create table WANT as

select A.SUBJID,

A.CA125DT

from HAVE A

where exists(select SUBJID from TABLE2 where SUBJID=A.SUBJID and A.CA125DT <= (STDT - 28));

quit;

topic Many to Many Merge - Exclude Based on Date Range in SAS Programming

Many to Many Merge - Exclude Based on Date Range

Re: Many to Many Merge - Exclude Based on Date Range

Re: Many to Many Merge - Exclude Based on Date Range