Many to Many Merge - Exclude Based on Date Range

[djbateman]

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

[gergely_batho]

Do an SQL join with the following join condition:

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

[RW9 · Accepted solution]

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;