scan part of fiel in one table against field in another tables

[podarum]

Hi, I have a table with cities and I want to match this City field against another tbales Address field that contains the same City embedded within the field.

HAVE

TABLE1

Address_Field                         Prov

ABC TORONTO ON                  ON

XX VANCOUVER BC                BC

NORTH YORK ON                    ON

WEST VANCOUVER BC           BC

MARKHAM XCD ON                  ON

SCARBOROUGH ON                 ON

                                                    QC

TABLE2

City                                       Prov      Price

TORONTO                             ON           3 

VANCOUVER                         BC          66

NORTH YORK                        ON           4

WEST VANCOUVER              BC          45

MARKHAM                              ON          35

QC                                           QC          42

ON                                           ON          37

I need to link the cities in Table2 (wiht their associated Price) with Table1's City in Address_Field.  If it's not in Table2 City then use the corresponding province (eg. QC to QC) or instead of Scarborough since it's not in Table2, use the corresponding province ON.

WANT

TABLE1

Address_Field                         Prov         City                          Price

ABC TORONTO ON                  ON        TORONTO                    3

XX VANCOUVER BC                BC       VANCOUVER                66

NORTH YORK ON                    ON       NORTH YORK                4

WEST VANCOUVER BC           BC      WEST VANCOUVER      45

MARKHAM XCD ON                  ON        MARKHAM                    35

SCARBOROUGH ON                 ON            ON                            37

                                                    QC             QC                           42

Thanks.

[LinusH]

Use SQL with CONTAINS to match city and address. Use DISTINCT to remove any duplicates if you also get a match on province.

[podarum]

I tried CONTAIN and it works, but how can I guarantee that the whoel word matches.. for example PE matched wiht WinniPEg?  I want Winnipeg to match to the whole word Winnipeg OR PE to PE only no mismatches.

[Yavuz]

There is: operators =: or in: can be used to subset a sas table in a sql statement if they are used as a dataset option instead of a sql clause.
proc sql;
select NAME from SASHELP.CLASS(where=(NAME in : ('A','B')));
quit;

[Ksharp]

There are many scenarios you need to take into account.

data TABLE1;
input Address_Field   & $40.      Prov $;
cards;
ABC TORONTO ON                  ON
XX VANCOUVER BC                BC
NORTH YORK ON                    ON
WEST VANCOUVER BC           BC
MARKHAM XCD ON                  ON
SCARBOROUGH ON                 ON
.                                               QC
;
run;

data TABLE2;
input City    & $40.         Prov    $  Price;
cards;
TORONTO                             ON           3 
VANCOUVER                         BC          66
NORTH YORK                        ON           4
WEST VANCOUVER              BC          45
MARKHAM                              ON          35
QC                                           QC          42
ON                                           ON          37
;
run;
proc sql;
create table temp as
select a.*,b.price
 from table1 as a left join 
 (select * from table2 where city is not missing and city ne prov) as b 
  on a.address_field like cats('%',b.city,'%');
  
create table want as
select a.address_field,a.prov,coalesce(a.price,b.price) as price
 from temp as a left join  
 (select * from table2 where city is not missing and city eq prov) as b 
  on a.prov=b.city;
  
quit;