Help using Base SAS procedures

Select records that contains variable from another table

Accepted Solution Solved
Reply
Regular Contributor
Posts: 186
Accepted Solution

Select records that contains variable from another table

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.


Accepted Solutions
Solution
‎03-11-2014 06:42 AM
Super User
Super User
Posts: 7,411

Re: Select records that contains variable from another table

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;

View solution in original post


All Replies
Super User
Posts: 10,516

Re: Select records that contains variable from another table

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

Regular Contributor
Posts: 186

Re: Select records that contains variable from another table

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

Solution
‎03-11-2014 06:42 AM
Super User
Super User
Posts: 7,411

Re: Select records that contains variable from another table

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;

Super User
Posts: 5,085

Re: Select records that contains variable from another table

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.

Occasional Contributor
Posts: 7

Re: Select records that contains variable from another table

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 ;
Regular Contributor
Posts: 186

Re: Select records that contains variable from another table

Both solutions work like a charm.

Thank you all for your help and time.

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 233 views
  • 6 likes
  • 5 in conversation