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

[deleted_user]

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

[Cynthia_sas]

Hi:
Take a look at the INDEX, INDEXC, FIND and FINDC functions.
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a002267763.htm
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000212242.htm
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a002264923.htm
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000212237.htm

or these have some useful information:
http://www.ats.ucla.edu/stat/sas/faq/index_function.htm
http://www.nesug.org/Proceedings/nesug01/bt/bt3002.pdf
http://analytics.ncsu.edu/sesug/2003/SE10-Cassidy.pdf

cynthia