Finding if values in 1 column are substring of values in 2 column

deleted_user — Sat, 10 Oct 2009 22:45:50 GMT

Hi,

I'm new on this forum and quite new with SAS coding too, so thanks for your help and your kind understanding of my "ignorance".
I am trying to link table 1 (1 column called client_name) to table 2 (1 column called advertiser_name) where table 2 contains a list of 5000 terms that could (or could not) be embedded into any of the client_names in table 1.

Here is an example

Table1
Client_name:
Walt Disney Corporation
Ebay Inc
.com Amazon account

Table2
Advertiser_name:
Amazon
Disney
Ebay

The output i need is a 3rd table that would look like this
Client_Name Advertiser_Name
Walt Disney Corporation Disney
Ebay Inc Ebay
.com Amazon account Amazon

Like %table2.advertiser_Name% would have been great but it seems the like operator only works with a constant.
I also tried this statement that did not work either:
Create table temp as
select t1.Company_name
,t2.Advertiser
from SSP.SSPClients as t2
inner join SSP.Datamart as t1
on t1.Company_name like '%' + t2.Advertiser + '%';
quit;

Company_name and Advertiser are both text fields (length of $44 and $36)

Is there any solution to resolve this problem.

THANK you SO MUCH for your help. I much appreciate it!
Julie

topic Finding if values in 1 column are substring of values in 2 column in SAS Procedures

Finding if values in 1 column are substring of values in 2 column

Re: Finding if values in 1 column are substring of values in 2 column