non-trivial algorithm for char fields (arrays, sorting, etc..)

[Ivan555]

Hello!

Performing one of the tasks, I got stuck and can not find the best way to solve the problem.

I have a table.

proc sql;
create table tmp (num int, name char(200));
insert into tmp
	values (1,'281.3891.3891.281')
	values (2,'3891.281.281.3891')
	values (3,'1162.5645.5645.500835.500835.1162')
	values (4,'5645.500835.500835.1162.1162.5645')
	values (5,'500835.1162.1162.5645.5645.500835')
	values (6,'1349.1162.1162.5645.5645.500835.500835.1349')
	values (7,'1162.5645.5645.500835.500835.1349.1349.1162')
	values (8,'5645.500835.500835.1349.1349.1162.1162.5645')
	values (9,'500835.1349.1349.1162.1162.5645.5645.500835');
quit;

Each line is a chain, and taking this into account, it is clear that the lines {1;2}, {3;4;5}, {6,7,8,9} are duplicates.

The question is, how would it be most correct to filter the rows so that only one row remains inside each of the duplicate groups?

(for example the lowest value of the first number like rows 1, 3, 7)

I was thinking of creating an array for each row and sorting the values ​​by a common shift within each array. But I'm not sure if this is the smartest way ..

THX!

[ballardw]

I am not exactly sure that I understand exactly what you want but see if this gets close.

proc sql;
create table tmp (num int, name char(200));
insert into tmp
	values (1,'281.3891.3891.281')
	values (2,'3891.281.281.3891')
	values (3,'1162.5645.5645.500835.500835.1162')
	values (4,'5645.500835.500835.1162.1162.5645')
	values (5,'500835.1162.1162.5645.5645.500835')
	values (6,'1349.1162.1162.5645.5645.500835.500835.1349')
	values (7,'1162.5645.5645.500835.500835.1349.1349.1162')
	values (8,'5645.500835.500835.1349.1349.1162.1162.5645')
	values (9,'500835.1349.1349.1162.1162.5645.5645.500835');
quit;

data tmp2;
   set tmp;
   array v (50) $ 6;
   length newname $200;
   do i= 1 to countw(name);
      v[i]= scan(name,i,'.');
   end;
   call sortc(of v(*));
   newname=catx('.',of v(*));
   drop v: i;
run;

proc sort data=tmp2 out=want nodupkey;
  by newname;
run;
   

[PGStats]

Another approach using SQL:

proc sql;
create table want as
select *
from have as a 
where not exists (
	select * from have as b
	where a.num > b.num and length(a.name) = length(b.name) and
		index(cats(".", b.name, ".", b.name,"."), cats(".", a.name, ".")) > 0);
quit;

[hashman]

@PGStats:

Wow ... finally a way of doing this purely in terms of sets without procedural code. A clever way to identify the cycles, too!

Kind regards

Paul D. 

[ChrisNZ]

@PGStats Why the surrounding dots? Isn't this enough?

	where a.num > b.num and length(a.name) = length(b.name) and
		index(cats( b.name, ".", b.name), strip( a.name )) 

[PGStats]

@ChrisNZ Adding delimiters might indeed not be necessary. It just seemed safer

[hashman]

@ChrisNZ:

Keeps getting still better. Which makes me wonder why the condition equating the lengths is needed, as the query does fine without it, too:

proc sql ;                                                                                                                              
  create table want as                                                                                                                  
  select * from have a                                                                                                                  
  where not exists                                                                                                                      
 (select 1 from have b                                                                                                               
  where  a.num > b.num                                                                                                                  
  and    find (cats (b.name, ".", b.name), trim (a.name))                                                                              
  ) ;                                                                                                                                   
quit ;    

Kind regards

Paul D.

[ChrisNZ]

> why the condition equating the lengths is needed

For speed: it's much faster to check the length than the cat() result.

[PGStats]

@hashman , @ChrisNZ ... requiring equal lengths is good for speed, yes, but mostly, it is essential to avoid matching substrings: 1.2.3.4.5.6 with 3.4.5

[FreelanceReinh]

---

@ChrisNZ wrote:

@PGStats Why the surrounding dots? Isn't this enough?

	where a.num > b.num and length(a.name) = length(b.name) and
		index(cats( b.name, ".", b.name), strip( a.name )) 

---

The surrounding delimiters are necessary. Try this input dataset:

data have;
input num name $;
cards;
1 111
2 1.1
;

[Reeza]

Does it have to be an exact match?
What if the 2nd row was missing one code but since the codes are duplicated?

ie

if 2 was
3891.281.3891 would it still be a duplicate?

[Ivan555]

@ballardw 

Thank you, I am checking

@Reeza  

Does it have to be an exact match?

Yes, exact match using parallel shift(can't catch how better to say).

3891.281.3891 would it still be a duplicate?

Value '3891.281.281.3891' and value '3891.281.3891.282' will not be dupllicates.

Perhars in start message there was placed not very good example.. groups {3;4;5} and {10;11;12} are different

proc sql;
create table tmp (num int, name char(200));
insert into tmp
	values (1,'281.3891.3891.281')
	values (2,'3891.281.281.3891')
	values (3,'1162.5645.5645.500835.500835.1162')
	values (4,'5645.500835.500835.1162.1162.5645')
	values (5,'500835.1162.1162.5645.5645.500835')
	values (6,'1349.1162.1162.5645.5645.500835.500835.1349')
	values (7,'1162.5645.5645.500835.500835.1349.1349.1162')
	values (8,'5645.500835.500835.1349.1349.1162.1162.5645')
	values (9,'500835.1349.1349.1162.1162.5645.5645.500835')
	values (10,'5645.1162.1162.500835.500835.5645')
	values (11,'1162.500835.500835.5645.5645.1162')
	values (12,'500835.5645.5645.1162.1162.500835');
quit;

p.s. While writing I am trying to do my best, but seems my English is rather unclear.. I am very Sorry for it..

[Ivan555]

3891.281.3891 would it still be a duplicate?

[Ivan555]

@ballardw 

It works and it is close, Thank you!

Could you say is it possible to improve your code according to my second example? - groups {3;4;5} and {10;11;12} should differ.

[Reeza]

What makes them different?

[Ivan555]

Values in 'name' field are looped chains.

If sequence of the elements is different, we have different chains.

Under numbers 3, 4, 5 there is the same sequence of the elements with different starting point; the chain is the same - so we are having duplicates.