Custom Date Merge

[newsas007]

Hi,

Below is my SAS code and the final 'Want' dataset. 

First rule is straight forward and have the code working for it. For example: ID#1 row 1: Line 2 falls between LOstart and  LOend. i.e. 7/16/2016 falls between 4/5/2016 and 8/16/2016

i need help with second rule. If the the ordering date doesn't fall between the specific LOstart and LOend, then search if it is between any 2 consecutive LINE's i.e. if it occurs between LOEnd of a line and beginning of next LOStart. For example ID#5 has a ordering date of 7/31/2016 and it does not fall between and specific date but it does fall between the LoEnd of Line = 2 and LoStart of Line = 3. So therefore it goes in line 2. ID#7 ordering date doesn't fall between LoStart and LoEnd; also doesn't have any consecutive line.

Data Want is below.

data dat1;
input ID	LINE (LOSTART	LOEND) (:mmddyy10.);
format LINE LOSTART LOEND mmddyy10.;
cards;
1	4	11/27/2017	1/17/2018
1	1	12/1/2015	4/1/2016
1	2	4/5/2016	8/16/2016
1	3	8/23/2016	11/14/2016
2	1	6/4/2016	9/18/2016
2	2	9/19/2016	11/11/2016
3	2	5/6/2016	7/17/2016
3	3	7/18/2016	10/3/2016
4	1	11/2/2015	5/30/2016
4	2	5/31/2016	12/28/2016
4	3	4/8/2018	1/9/2019
5	1	4/3/2015	6/4/2015
5	2	6/5/2015	12/27/2015
5	3	8/5/2016	9/7/2016
6	1	10/12/2015	6/2/2016
6	4	11/7/2016	4/20/2017
6	2	6/3/2016	8/27/2016
7	1	6/5/2016	6/15/2016
;

data dat2;
input ID	Ordering :mmddyy10.;
format ordering mmddyy10.;
cards;
1	7/16/2016
1	10/2/2017
2	7/18/2016
3	7/18/2016
4	7/26/2016
5	7/31/2016
6	7/31/2016
7	5/10/2016
;

proc sql;
create table ab  as
select distinct a.*, b.ordering
from dat1 a left join dat2 b
on a.ID=b.ID and LOSTART<=Ordering<=LOEND
order by a.ID;
quit;

Data Want:

ID	LINE	LOSTART	       LOEND	Ordering
1	First	12/1/2015	4/1/2016	.
1	Fourth	11/27/2017	1/17/2018	.
1	Second	4/5/2016	8/16/2016	7/16/2016
1	Third	8/23/2016	11/14/2016	.
2	First	6/4/2016	9/18/2016	7/18/2016
2	Second	9/19/2016	11/11/2016	.
3	Second	5/6/2016	7/17/2016	.
3	Third	7/18/2016	10/3/2016	7/18/2016
4	First	11/2/2015	5/30/2016	.
4	Second	5/31/2016	12/28/2016	7/26/2016
4	Third	4/8/2018	1/9/2019	.
5	First	4/3/2015	6/4/2015	.
5	Second	6/5/2015	12/27/2015	7/31/2016
5	Third	8/5/2016	9/7/2016	.
6	First	10/12/2015	6/2/2016	.
6	Fourth	11/7/2016	4/20/2017	.
6	Second	6/3/2016	8/27/2016	7/31/2016
7	First	6/5/2016	6/15/2016	.

[MayurPhegde]

Check if below code helps: 

proc sort data=dat1 out=dat1_1;
by id line;
run;


proc sql;
create table ab as
select distinct a.*, coalesce(b.ordering,d.ordering) fromat=mmddyy10. as ordering
from dat1 a
left join dat2 b
on a.ID=b.ID and LOSTART<=Ordering<=LOEND
left join dat1 c
on a.ID=c.ID and a.line = c.line - 1
left join dat2 d
on c.ID=d.ID and a.LOEND<=d.Ordering<=c.LOSTART
order by a.ID;
quit;

Output:

ID	LINE	LOSTART	LOEND	ordering
1	1	12/1/2015	4/1/2016	.
1	2	4/5/2016	8/16/2016	7/16/2016
1	3	8/23/2016	11/14/2016	10/2/2017
1	4	11/27/2017	1/17/2018	.
2	1	6/4/2016	9/18/2016	7/18/2016
2	2	9/19/2016	11/11/2016	.
3	2	5/6/2016	7/17/2016	7/18/2016
3	3	7/18/2016	10/3/2016	7/18/2016
4	1	11/2/2015	5/30/2016	.
4	2	5/31/2016	12/28/2016	7/26/2016
4	3	4/8/2018	1/9/2019	.
5	1	4/3/2015	6/4/2015	.
5	2	6/5/2015	12/27/2015	7/31/2016
5	3	8/5/2016	9/7/2016	.
6	1	10/12/2015	6/2/2016	.
6	2	6/3/2016	8/27/2016	7/31/2016
6	4	11/7/2016	4/20/2017	.
7	1	6/5/2016	6/15/2016	.

[newsas007]

@MayurPhegde : Thank you for this. There seems to be some issue with this. For example, the code below outputs ordering date (7/18/2016) twice for ID#3.

data dat1;
input ID	LINE	(LOSTART	LOEND) (:mmddyy10.);
format 	LOSTART	LOEND mmddyy10.;
cards;
1	4	11/27/2017	1/17/2018
1	1	12/1/2015	4/1/2016
1	2	4/5/2016	8/16/2016
1	3	8/23/2016	11/14/2016
2	1	6/4/2016	9/18/2016
2	2	9/19/2016	11/11/2016
3	2	5/6/2016	7/17/2016
3	3	7/18/2016	10/3/2016
4	1	11/2/2015	5/30/2016
4	2	5/31/2016	12/28/2016
4	3	4/8/2018	1/9/2019
5	1	4/3/2015	6/4/2015
5	2	6/5/2015	12/27/2015
5	3	8/5/2016	9/7/2016
6	1	10/12/2015	6/2/2016
6	4	11/7/2016	4/20/2017
6	2	6/3/2016	8/27/2016
7	1	6/5/2016	6/15/2016
7   2   8/15/2016   9/15/2016
7   3   9/16/2016   10/16/2016 
;

data dat2;
input ID	Ordering :mmddyy10.;
format ordering mmddyy10.;
cards;
1	7/16/2016
1	10/2/2017
2	7/18/2016
3	7/18/2016
4	7/26/2016
5	7/31/2016
6	7/31/2016
7	7/10/2016
;

proc sql;
create table ab as
select distinct a.*, coalesce(b.ordering,d.ordering) format=mmddyy10. as ordering
from dat1 a
left join dat2 b
on a.ID=b.ID and LOSTART<=Ordering<=LOEND
left join dat1 c
on a.ID=c.ID and a.line = c.line - 1
left join dat2 d
on c.ID=d.ID and a.LOEND<=d.Ordering<=c.LOSTART
order by a.ID;
quit;

[MayurPhegde]

Its because of below condition
c.ID=d.ID and a.LOEND<=d.Ordering<=c.LOSTART

Replace it with
c.ID=d.ID and a.LOEND<d.Ordering<c.LOSTART


It should work.