topic Re: scan part of fiel in one table against field in another tables in SAS Programming

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

podarum — Sun, 26 Feb 2017 03:30:35 GMT

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

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.

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

LinusH — Sun, 26 Feb 2017 06:47:18 GMT

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

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

podarum — Sun, 26 Feb 2017 07:32:43 GMT

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.

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

Yavuz — Sun, 26 Feb 2017 08:36:14 GMT

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;

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

Ksharp — Sun, 26 Feb 2017 11:12:41 GMT

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;