topic Re: Select records that contains variable from another table in SAS Procedures

Select records that contains variable from another table

nicnad — Mon, 10 Mar 2014 22:12:46 GMT

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.

Re: Select records that contains variable from another table

ballardw — Mon, 10 Mar 2014 22:36:14 GMT

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

Re: Select records that contains variable from another table

nicnad — Tue, 11 Mar 2014 01:39:43 GMT

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

Re: Select records that contains variable from another table

RW9 — Tue, 11 Mar 2014 10:42:16 GMT

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;

Re: Select records that contains variable from another table

Astounding — Tue, 11 Mar 2014 13:55:40 GMT

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.

Re: Select records that contains variable from another table

chrisj75 — Tue, 11 Mar 2014 14:00:36 GMT

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 ;

Re: Select records that contains variable from another table

nicnad — Tue, 11 Mar 2014 14:59:17 GMT

Both solutions work like a charm.

Thank you all for your help and time.