SQL date merge

[Kurt_Bremser]

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.

[rajd1]

Thank you so much for this!
Just a little edit:
line=3 for id=1 row 2 and line=2 for id=5

when order date is not in between LOstart and LOend, then Line = before the start of next line. For example: ID#1 row 2: order date 10/2/2017 is considered line 3 because it occurs before the beginning of line 4 LOstart date of 11/27/2018.
Similarly with ID#5 order date is 7/31/2016 is considered line 2 because it occurs before the beginning of line 3 LOstart date of 8/5/2016

[Kurt_Bremser]

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;