BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
angeliquec
Quartz | Level 8

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20
angeliquec
Quartz | Level 8

2|9 is "WHITE ROAD". 

1|4 is "GREEN ROAD".

 

 

novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Tourmaline | Level 20

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;

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 2575 views
  • 0 likes
  • 3 in conversation