Hi,
This gets very close, thank you. The below code restricts to 1st, 2nd and 3rd prior record but I need to combine records only if there are 3 consecutive dates from table 2. See below table for example of records to keep (yes) and not keep (no). I apologize if I wasn't clear on my original post.
proc sql; create table WANT as select distinct t1.ID, t1.DATE, t2.STARTDATE, (t1.DATE-t2.STARTDATE) as TIME, t2.* from TABLE1 as t1 join TABLE2 as t2 on (t1.ID=t2.ID) and (t1.DATE - t2.STARTDATE) in (1,2,3) order by ID, Date, STARTDATE desc; quit;
ID
DATE
STARTDATE
TIME
KEEP
NOTE
2022
9/30/2014
9/29/2014
1
YES
This consecutive group contains (1, 2 & 3)
2022
9/30/2014
9/28/2014
2
YES
2022
9/30/2014
9/27/2014
3
YES
2022
10/1/2014
9/29/2014
2
NO
This consecutive group does not include (1)
2022
10/1/2014
9/28/2014
3
NO
2091
10/1/2014
9/30/2014
1
NO
This non-consecutive group does not include (2)
2091
10/1/2014
9/28/2014
3
NO
2092
10/8/2014
10/7/2014
1
NO
This consecutive group does not include (3)
2092
10/8/2014
10/6/2014
2
NO
2092
10/10/2014
10/9/2014
1
YES
This consecutive group contains (1, 2 & 3)
2092
10/10/2014
10/8/2014
2
YES
2092
10/10/2014
10/7/2014
3
YES
2094
11/3/2014
11/2/2014
1
NO
This consecutive group does not include (3)
2094
11/3/2014
11/1/2014
2
NO
2094
11/5/2014
11/2/2014
3
NO
This only contains (3)
2094
11/7/2014
11/6/2014
1
YES
This consecutive group contains (1, 2 & 3)
2094
11/7/2014
11/5/2014
2
YES
2094
11/7/2014
11/4/2014
3
YES
2099
12/3/2014
12/2/2014
1
NO
This consecutive group does not include (3)
2099
12/3/2014
12/1/2014
2
NO
... View more
