BookmarkSubscribeRSS Feed
altijani
Quartz | Level 8

Greetings Community,

 

I have the following data (HAVE), and would like to match between two different addresses that our customers have.

Street NumberStreet NameAddress CityStateZIPAddress 2
12MAIN STREETGREENSBORONC1234512 MAIN ST, GREENSBORO NC 12345
56SIDE ROADRALEIGHNC5423156 SIDE RD, RALEIGH NC 54231
78WALNUT BLVD.CARYNC12300WALNUT BOLUVARD 78, CARY NC12300
90SIDE PL.IVYNC12340PO BOX 123, IVY NC 12340
34PAUL GARDENSAPEXNC12350PAUL GRDNS. 34 APT C1, APEX NC 12350

 

 

I need an additional column to specify whether the two addresses are the same in at least BOTH the "Street Number" and the "Street Name", otherwise it is not a match:

 

Street NumberStreet NameAddress CityStateZIPAddress 2Match
12MAIN STREETGREENSBORONC1234512 MAIN ST, GREENSBORO NC 12345YES
56SIDE ROADRALEIGHNC5423156 SIDE RD, RALEIGH NC 54321YES
78WALNUT BLVD.CARYNC12300WALNUT BOLUVARD 78, CARY NC12300YES
90SIDE PL.IVYNC12340PO BOX 123, IVY NC 12340NO
34PAUL GARDENSAPEXNC12350PAUL GRDNS. 34 APT C1, APEX NC 12350YES

 

Many thanks!

1 REPLY 1
LaurieF
Barite | Level 11

The comparison bit is easy: catx(' ', street_number, street_name) = address_2.

 

What's hard, but not hugely, is to find the street-type identifier (street, road, boulevard, grove, lane, alley; usually the last word) and standardise it on both sides. My method is to abbreviate it in both places (street -> st, grove -> gv) and strip out all punctuation, including commas and full-stops. I'd also recommend setting them both to upper or lower case.

 

I had initial problems when I was working in England with a town called Street, but because the meaning of the words isn't important, the match worked perfectly.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1 reply
  • 550 views
  • 0 likes
  • 2 in conversation