BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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.

 

4 REPLIES 4
LinusH
Tourmaline | Level 20
Use SQL with CONTAINS to match city and address. Use DISTINCT to remove any duplicates if you also get a match on province.
Data never sleeps
podarum
Quartz | Level 8

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
Quartz | Level 8
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
Super User

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;
 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 795 views
  • 1 like
  • 4 in conversation