topic Re: How to Output Overlap Ranges in SAS Programming

How to Output Overlap Ranges

angeliquec — Thu, 30 Aug 2018 03:13:23 GMT

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

/*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;

Re: How to Output Overlap Ranges

novinosrin — Thu, 30 Aug 2018 04:04:27 GMT

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;

Re: How to Output Overlap Ranges

ChrisNZ — Thu, 30 Aug 2018 04:51:44 GMT

How does 2|9 not overlap 1|4 ?

Re: How to Output Overlap Ranges

angeliquec — Thu, 30 Aug 2018 05:06:35 GMT

2|9 is "WHITE ROAD".

1|4 is "GREEN ROAD".

Re: How to Output Overlap Ranges

novinosrin — Thu, 30 Aug 2018 05:09:55 GMT

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

Re: How to Output Overlap Ranges

novinosrin — Thu, 30 Aug 2018 05:18:00 GMT

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;

Re: How to Output Overlap Ranges

ChrisNZ — Thu, 30 Aug 2018 05:20:24 GMT

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;

Re: How to Output Overlap Ranges

ChrisNZ — Thu, 30 Aug 2018 05:27:50 GMT

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;

Re: How to Output Overlap Ranges

novinosrin — Thu, 30 Aug 2018 05:28:09 GMT

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