How to Output Overlap Ranges

[angeliquec]

Hi, I'd like to output which ranges overlap (no fixing of affected ranges required). 

data HAVE;
infile datalines dlm="|";
input NUM1 NUM2 ADDRESS :$12. CITY :$12.;
datalines;
0|2|GREEN ROAD|RED CITY
1|4|GREEN ROAD|RED CITY
3|4|GREEN ROAD|RED CITY
5|7|GREEN ROAD|RED CITY
2|9|WHITE ROAD|RED CITY
;
run;

/*Output affected Ranges*/
data WANT;
infile datalines dlm="|";
input NUM1 NUM2 ADDRESS :$12. CITY :$12.;
datalines;
0|2|GREEN ROAD|RED CITY
1|4|GREEN ROAD|RED CITY
3|4|GREEN ROAD|RED CITY
;
run;

[novinosrin · Accepted solution]

I hope this meets your requirement

data HAVE;
infile datalines dlm="|";
input NUM1 NUM2 ADDRESS :$12. CITY :$12.;
datalines;
0|2|GREEN ROAD|RED CITY
1|4|GREEN ROAD|RED CITY
3|4|GREEN ROAD|RED CITY
5|7|GREEN ROAD|RED CITY
2|9|WHITE ROAD|RED CITY
;
run;

data temp;
set have;
retain f 0;
If _n_>1 and not(num1<lag(num2)) then f+1;
else f=0;
run;

data want;
set temp;
by f notsorted;
if not(first.f and last.f) and f=0 then output ;
drop f;
run;

[ChrisNZ]

How does 2|9 not overlap 1|4 ?

[angeliquec]

2|9 is "WHITE ROAD". 

1|4 is "GREEN ROAD".

[novinosrin]

Oh well, in that case my logic doesnt account for address groups. I may have to tweak, but too late at night in chicago now or i hope somebody else would give you more appropriate solution

[novinosrin]

see if this helps. Please test thoroughly

data temp;
set have;
by city address notsorted;
retain f;
if first.address then f=0;
if not first.address and not(num1<lag(num2)) then f+1;
else f=0;
run;

data want;
set temp;
by city address f notsorted;
if not(first.f and last.f) and f=0 then output ;
drop f;
run;

[ChrisNZ]

Of course, sorry about the silly question.

The solution you picked does not spot the last overlap I think

data HAVE;
 infile datalines dlm="|";
 input NUM1 NUM2 ADDRESS :$12. CITY :$12.;
datalines;
0|2|GREEN ROAD|RED CITY
1|4|GREEN ROAD|RED CITY
3|4|GREEN ROAD|RED CITY
5|7|GREEN ROAD|RED CITY
6|9|GREEN ROAD|RED CITY
run;

[novinosrin]

Thank you

data temp;
set have;
by city address notsorted;
retain f;
if first.address then f=0;
if not first.address and not(num1<lag(num2)) then f+1;
run;

data want;
set temp;
by city address f notsorted;
if not(first.f and last.f)  then output ;
drop f;
run;

sir @ChrisNZ will this work. too tired now, but will pay serious attention tomorrow morning

[ChrisNZ]

Like this?

proc sql;
   select unique t1.* 
   from  HAVE t1, HAVE t2
   where t1.ADDRESS=t2.ADDRESS 
     and t1.CITY=t2.CITY 
     and ( (t1.NUM1 < t2.NUM2 < t1.NUM2) 
          |(t1.NUM1 < t2.NUM1 < t1.NUM2)
         );
quit;