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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.