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 - -
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.