DATA Step, Macro, Functions and more

Many to Many Merge - Exclude Based on Date Range

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
Accepted Solution

Many to Many Merge - Exclude Based on Date Range

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

SUBJIDCA125DT
0001-331424-Sep-12
0001-331422-Oct-12
0001-33145-Nov-12
0001-331419-Nov-12
0001-331417-Dec-12
0001-331414-Jan-13
0001-331411-Feb-13
0001-331415-Feb-13
0008-414615-Oct-12
0014-438727-Dec-13
0014-438710-Feb-14
0014-438721-Mar-14
0014-438723-May-14
0020-00201-Jul-13
0020-002026-Aug-13
0020-00203-Sep-13
0020-00933-Mar-14
0020-009331-Mar-14
0020-009314-Apr-14
0020-009312-May-14
0020-009310-Jun-14
0020-414217-Sep-11

TABLE2

SUBJIDSTDT
0001-331419-Jul-12
0001-331431-Aug-12
0001-331424-Oct-12
0001-331419-Nov-12
0001-331421-Dec-12
0001-331418-Jan-13
0001-331422-Feb-13
0001-33149-Mar-13
0008-414621-Aug-12
0008-414631-Aug-12
0008-414612-Sep-12
0008-414621-Sep-12
0008-414627-Sep-12
0008-41469-Oct-12
0008-414619-Oct-12
0008-414626-Oct-12
0008-41468-Nov-12
0014-43878-Nov-13
0014-438726-Nov-13
0014-43874-Dec-13
0020-00202-Jul-13
0020-002029-Jul-13
0020-00205-Sep-13
0020-009328-May-14
0020-414213-Sep-11

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

SUBJIDCA125DT
0001-331422-Oct-12
0014-438721-Mar-14
0014-438723-May-14
0020-00201-Jul-13
0020-00203-Sep-13
0020-00933-Mar-14
0020-009331-Mar-14
0020-009314-Apr-14
0020-009312-May-14

Accepted Solutions
Solution
‎04-13-2015 10:30 AM
Super User
Super User
Posts: 7,401

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

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;

View solution in original post


All Replies
SAS Employee
Posts: 340

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

Do an SQL join with the following join condition:

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

Solution
‎04-13-2015 10:30 AM
Super User
Super User
Posts: 7,401

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

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 154 views
  • 3 likes
  • 3 in conversation