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 .
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 | . |
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.