topic Re: Data type mismatch causing issue with PROC SQL in Developers

Data type mismatch causing issue with PROC SQL

TS_Hum — Thu, 01 Jul 2021 21:48:31 GMT

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;

Re: Data type mismatch causing issue with PROC SQL

PaigeMiller — Thu, 01 Jul 2021 21:59:37 GMT

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.

Re: Data type mismatch causing issue with PROC SQL

FreelanceReinh — Fri, 02 Jul 2021 08:17:27 GMT

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.

Re: Data type mismatch causing issue with PROC SQL

TS_Hum — Fri, 02 Jul 2021 12:07:15 GMT

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

Re: Data type mismatch causing issue with PROC SQL

TS_Hum — Fri, 02 Jul 2021 12:12:30 GMT

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.

Re: Data type mismatch causing issue with PROC SQL

FreelanceReinh — Fri, 02 Jul 2021 12:54:45 GMT

@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.

Re: Data type mismatch causing issue with PROC SQL

TS_Hum — Fri, 02 Jul 2021 13:14:06 GMT

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