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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.