BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nicnad
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
ballardw
Super User

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

nicnad
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Calcite | Level 5

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
Fluorite | Level 6

Both solutions work like a charm.

Thank you all for your help and time.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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