To find if elements under a group are the substring of another string ?

[vrushankshah]

I want to find if the given elements under a group are the part of another string or no ? If they are then I want the group number of the string where it was the part. They should be part of another string.For example, 'Benefits, verify' is one string under group 1 and that is part of group 2 string (claims,verify,benefits,verify). I also want to count how many times it appears in another string.

For example

Column1                            group
Benefits,verify                      1
claims,verify,benefits,verify        2
inquiry,type                         3
claims,verify                        4

Output expected:

column1                        Part of group      count
Benefits, verify                 2                  1
claims,verify                    2                  1
inquiry,type                     -                  -
claims,verify,benefits,verify    -                  -

[Ksharp · Accepted solution]

data have;
input Column1 $35.                        group;
cards;
Benefits,verify                      1
claims,verify,benefits,verify        2
inquiry,type                         3
claims,verify                        4
;
run;

proc sql;
create table temp as
 select a.column1,b.group,count(*) as n
  from have as a , have as b
   where upcase(b.column1) contains upcase(strip(a.column1)) and a.group ne b.group
    group by a.column1;
create table want as
select * from temp
union
select column1,0,0 from have where column1 not in (select column1 from temp) ;
quit;