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.
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.
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: