|Date: ||Wed, 20 Nov 2002 19:56:33 -0500|
|Reply-To: ||"Michael D. Boldin" <mboldin@MINDSPRING.COM>|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||"Michael D. Boldin" <mboldin@MINDSPRING.COM>|
|Organization: ||MindSpring Enterprises|
|Subject: ||Using contains with SQL|
I have a dataset with about 100 company names and I want find close matches
to these names in another set that has 20,000 company names and an
associated ID number. I know that there are numerous data step ways to do
this for a single company ie where cname contains "ABC Corp";
and I could use these ways in a macro loop but I thought there would be more
elegant and flexible SQL way to handle all names in a single pass.
Unfortunately, all of may efforts to use 'contains' 'like' or 'index()' in
the same way as
on a.cname = b.name gives an error or this note
66 proc sql;
67 create table temp2
68 as select *
69 from temp1 as b
70 left join cnames as a
71 on a.cname contains b.cname1;
NOTE: The execution of this query involves performing one or more Cartesian
product joins that can not be optimized.
NOTE: Table WORK.TEMP2 created, with 5 rows and 15 columns.
and no joins are made (all comuns in temp2 are filled with missing values
any ideas or is this hopeless?