I am having some troubles with cleaning these addresses. at first it was easy with some if statement. But since my data is getting bigger, it is not efficient to use if statement anymore. I would love to hear your thoughts about how to clean this field.
input address $200;
100 ABC ST RM S02A
102 ABC STREET FLOOR 1
103 ABC ST APT 3 HOMELESS
1035 CD AVENUE FLOOR 2
108 SOMETHING ST # 2FL
115 VISA VISTA DR APT 212 APT 212
1155 LOOK AVENUE APT 205
12 BORED AVE APT 2
1214 TIRED STREET APT 428
127 HAPPY STREET FLOOR 2
1397 SOMEWHERE STREET FIRST FLOOR
142 SOMETHING ST APT 3
200 RAINBOW AVE UNIT 202
I don't want any Unit or floor or apt number in the clean address. So I want the address field that would look like this:
What is your actual use case? If your address cleaning needs to scale up to enterprise-wide customer volumes and techniques then you would be better off using a tool specific to this task like SAS Data Quality. On the other hand, cleaning a few hundred addresses with a few transformation rules like the ones in your post, you are probably better off persevering with your current approach.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.