Select records that contains variable from another table

[nicnad]

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.

[ballardw]

Do the names have to be exact matches or is ROBERT a match for ROBERTSON and similar compound names?

[nicnad]

I'd like to know both procedures, but exact match would do it for right now.

[RW9 · Accepted solution]

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;

[Astounding]

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.

[chrisj75]

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 ;

[nicnad]

Both solutions work like a charm.

Thank you all for your help and time.