Data type mismatch causing issue with PROC SQL

[TS_Hum]

Why are Res1 and Res2 always zero?  I reduced my problem to the code below.  I assume the issue is being caused by a mismatch of the data types, but I am not understanding how to address it.  Any help is appreciated.

/*SETUP*/
proc sql;
create table MyListTable (
MyVal varchar(100)
);
quit;

proc sql;
insert into MyListTable
set MyVal='AR'
set MyVal='ES';
quit;

/*THE PROBLEM*/
proc sql;
create table test as
select b.memname, a.MyVal
, index(a.MyVal, 'AR') as A_Found
, index(b.memname, 'AR') as B_Found
, index(upcase(b.memname), upcase(a.myval)) as Res1
, case when upcase(b.memname) like ('%' || a.MyVal || '%') then 1 else 0 end as Res2
from MyListTable a, DICTIONARY.TABLES b
where b.libname='SASHELP'
and b.memname like '%AR%';
quit;

[PaigeMiller]

When you get an error message, then 100% of the time you need to show us the LOG for this PROC, the entire log for this PROC, all of it, every single character of the log for this PROC, do not select and choose parts of the log for this PROC. That way we can see the code, the ERROR message(s), the WARNING message(s) and the NOTE message(s). Copy the LOG as text and paste it into the box that appears when you click on the </> icon.

[TS_Hum]

My apologies... I will try to remember to include that in future posts.  Thank you.

[FreelanceReinh · Accepted solution]

Hello @TS_Hum,

In your examples variable MyVal contains 98 trailing blanks. You need to remove them, e.g., with the TRIM function when using MyVal in the INDEX function or in concatenations with the || operator. So, just replace a.myval with trim(a.myval) in both places.

[TS_Hum]

Thanks so much!  I have an SQL background and am still getting used to SAS nuances.  When I initially got the results and it didn't match what I expected, I started going down this path thinking the spaces were being counted, but I stopped short of using a trim function after I used the LENGTH function and found that it was showing me the correct length of each value.  Obviously, it didn't count everything.  Is there another function that shows the length of everything, spaces included?  In SQL, there is LEN (similar to length here), but then there is also DATALENGTH(), which shows everything.

[FreelanceReinh]

---

@TS_Hum wrote:

Is there another function that shows the length of everything, spaces included?  In SQL, there is LEN (similar to length here), but then there is also DATALENGTH(), which shows everything.

---

Yes, there is: the LENGTHC function.

[TS_Hum]

Fantastic!  Thanks so much for your help (and patience)!  I am new to this.