Hi,
I'd like to achieve the following task in SAS SQL language.
I have two tables :
Table MYNAMETABLE :
NAME |
---|
NICOLAS |
ROBERT |
PETER |
Table MYCOMMENTTABLE
COMMENT |
---|
NICOLAS IS HAVING FUN |
ROBERT LOVE SAS |
THIS MORNING PETER ATE AN APPLE |
BILL LOVES ORANGE |
THIS EVENING PETER WILL DRIVE HIS CAR |
Now I would like to select each records from the table MYCOMMENTTABLE that contains any name from the table MYNAMETABLE.
How do I achieve that?
Thank you for your help and time.
Hi,
Try this. It basically merges all names onto all comments (so if you have big tables probably not ideal) then checks if the word exists (you could of course change the index for other types of checks):
proc sql;
create table WORK.WANT as
select distinct
COMMENT
from (
select A.COMMENT,
B.NAME
from (select 1 as IDNUM,
COMMENT
from MYCOMMENTTABLE) A
full join (select 1 as IDNUM,
NAME
from MYNAMETABLE) B
on A.IDNUM=B.IDNUM
)
where index(COMMENT,strip(NAME))>0;
quit;
Do the names have to be exact matches or is ROBERT a match for ROBERTSON and similar compound names?
I'd like to know both procedures, but exact match would do it for right now.
Hi,
Try this. It basically merges all names onto all comments (so if you have big tables probably not ideal) then checks if the word exists (you could of course change the index for other types of checks):
proc sql;
create table WORK.WANT as
select distinct
COMMENT
from (
select A.COMMENT,
B.NAME
from (select 1 as IDNUM,
COMMENT
from MYCOMMENTTABLE) A
full join (select 1 as IDNUM,
NAME
from MYNAMETABLE) B
on A.IDNUM=B.IDNUM
)
where index(COMMENT,strip(NAME))>0;
quit;
To understand "both procedures", just study the difference between the INDEX and the INDEXW functions. Either could be used in RW9's solution, but they produce slightly different results. You are probably looking to start with INDEXW.
Using the LIKE operator :
proc sql ; create table matches as select a.* from mycommenttable a inner join mynamestable b on a.comment like cats('%',b.name,'%') ; quit ;
Both solutions work like a charm.
Thank you all for your help and time.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.