Desktop productivity for business analysts and programmers

LIKE COLUMN

Accepted Solution Solved
Reply
Highlighted
Senior User
Posts: 1
Accepted Solution

LIKE COLUMN

 

Hi,

 

I am doing an address matching exercise where I want to match on part of an address being within another address. Is below correct?

 

I want to find t2.address_line_3_clean within the concatenation.

 

 cats(t1.address1, t1.address2, t1.address3) LIKE "%" || t2.address_line_3_clean || "%"

Regards

Finbar


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 11,101

Re: LIKE COLUMN

Also you likely do not want to use CATS as the last "word" of address1 will run into the first "word" of address2 creating a string that is less likely to be "like" your target. Please see this example and a suggested alternate:

data example;
   length address1 - address3 $ 25;
   address1 = 'Apartment 456  ';
   address2 = '123 Main St    ';
   address3 = 'SomeTown       ';
   resultcats = cats(address1,address2,address3);
   resultcatx = catx(' ',address1,address2,address3);
   put resultcats= resultcatx=;
run;

And really, provide the entire code as LIKE is only valid in Proc Sql/fedsql and so may have other issues, plus at least one example of values that this doesn't work for.

 

 

You may also want to construct a variable with the contents of "%" || t2.address_line_3_clean || "%"

to see exactly what you are comparing too as there is a good chance when using the || operator that your comparison string has a number of trailing spaces. From the documentation:

Note: When you are using the % character, be aware of the effect of trailing blanks. You might have to use the TRIM function to remove trailing blanks in order to match values.

View solution in original post


All Replies
Super User
Posts: 7,378

Re: LIKE COLUMN

What happened when you tried it?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 386

Re: LIKE COLUMN

Use Perl Regular Expression. See doc for details.
Solution
2 weeks ago
Super User
Posts: 11,101

Re: LIKE COLUMN

Also you likely do not want to use CATS as the last "word" of address1 will run into the first "word" of address2 creating a string that is less likely to be "like" your target. Please see this example and a suggested alternate:

data example;
   length address1 - address3 $ 25;
   address1 = 'Apartment 456  ';
   address2 = '123 Main St    ';
   address3 = 'SomeTown       ';
   resultcats = cats(address1,address2,address3);
   resultcatx = catx(' ',address1,address2,address3);
   put resultcats= resultcatx=;
run;

And really, provide the entire code as LIKE is only valid in Proc Sql/fedsql and so may have other issues, plus at least one example of values that this doesn't work for.

 

 

You may also want to construct a variable with the contents of "%" || t2.address_line_3_clean || "%"

to see exactly what you are comparing too as there is a good chance when using the || operator that your comparison string has a number of trailing spaces. From the documentation:

Note: When you are using the % character, be aware of the effect of trailing blanks. You might have to use the TRIM function to remove trailing blanks in order to match values.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 139 views
  • 3 likes
  • 4 in conversation