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

Solved
Occasional Contributor
Posts: 16

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

[ Edited ]

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 - -

Accepted Solutions
Solution
a month ago
Super User
Posts: 10,850

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

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;

All Replies
Solution
a month ago
Super User
Posts: 10,850

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

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;
☑ This topic is solved.