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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

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 ?

Posted in reply to vrushankshah
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;

View solution in original post


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 ?

Posted in reply to vrushankshah
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 55 views
  • 0 likes
  • 2 in conversation