Anyway, try this:
data a;
infile datalines dlm='09'x dsd truncover;
input ID LINE (LOSTART LOEND) (:mmddyy10.);
format LOSTART LOEND e8601da10.;
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 b;
infile datalines dlm='09'x dsd truncover;
input ID Order :mmddyy10.;
format order e8601da10.;
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 want as
select
b.id,
b.order,
case
when a.line ne .
then a.line
else (
case
when b.order lt (select min(a.lostart) from a where a.id=b.id)
then 0
else (
select line
from a
where a.id = b.id and b.order le a.lostart
group by a.id
having a.lostart = min(a.lostart)
)
end
)
end as line
from b left join a
on a.id = b.id and b.order between a.lostart and a.loend
;
quit;
It creates your expected result, with the exception of line=3 for id=5.
And I'd add a provision for an order that falls after the maximum loend:
data b;
infile datalines dlm='09'x dsd truncover;
input ID Order :mmddyy10.;
format order e8601da10.;
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
7 5/10/2017
;
proc sql;
create table want as
select
b.id,
b.order,
case
when a.line ne .
then a.line
else (
case
when b.order lt (select min(a.lostart) from a where a.id = b.id)
then 0
when b.order gt (select max(a.loend) from a where a.id = b.id)
then 99999999
else (
select line
from a
where a.id = b.id and b.order le a.lostart
group by a.id
having a.lostart = min(a.lostart)
)
end
)
end as line
from b left join a
on a.id = b.id and b.order between a.lostart and a.loend
order by id, order
;
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.