Help using Base SAS procedures

Case Sensitivity with "Like" in Proc SQL

Reply
Occasional Contributor
Posts: 9

Case Sensitivity with "Like" in Proc SQL

I ran into a situation where I wasn't getting the expected reults in a Proc SQL query. The line on my select statement was: "sum(case when table.name LIKE '%stove%' then 1 else 0 end) as TotalCount. I wasn't getting the correct results and discovered that the LIKE function is case-sensitive, causing the problem. However, I also discovered if I change the above line to "count(*) as TotalCount" and then use the like statement in the left join line: "LEFT JOIN table on table.id = root.id and table.name LIKE '%stove%' ", it isn't case-sensitive.

1st question, is there a way to make the LIKE function case-insenstive all the time? 2nd question, does anyone know why it would be case-sensitive in certain situations and not in others?
Frequent Contributor
Posts: 127

Re: Case Sensitivity with "Like" in Proc SQL

Hi,

Using the following statement will probably fit with your needs:

sum(case when upcase(table.name) LIKE '%STOVE%' then 1 else 0 end) as TotalCount

Regards,
Florent
Ask a Question
Discussion stats
  • 1 reply
  • 1794 views
  • 0 likes
  • 2 in conversation