topic Re: Find value that are lowercase in where clause in SAS Enterprise Guide

Find value that are lowercase in where clause

ljim1075 — Fri, 22 Oct 2021 16:08:32 GMT

I have a field_name that it's format is $1, meaning 1 character value. I'm trying to pull the values that are in lowercase ONLY. Can you assist?

query:

proc print data=tera.Table_A (obs=10);
where field_name in ('b');
run;

When I run the above query it returns all uppercase 'B' and lowercase 'b'

Re: Find value that are lowercase in where clause

Reeza — Fri, 22 Oct 2021 16:16:27 GMT

@ljim1075 wrote:

I have a field_name that it's format is $1, meaning 1 character value. I'm trying to pull the values that are in lowercase ONLY. Can you assist?

query:

proc print data=tera.Table_A (obs=10);
where field_name in ('b');
run;

When I run the above query it returns all uppercase 'B' and lowercase 'b'

Are you sure that's what's happening?

Can you post the output and log?

That shouldn't happen AFAIK.

Also, is tera.table_A a SAS dataset or is it a data set from a server? If server, what type?

EDIT: Cannot replicate your issue, does the example below run as expected

data have;
input field_name $1.;
cards;
b
B
a
c
C
d
d
D
b
b
B
f
;
run;

proc print data=have(obs=10);
where field_name in ('b');
run;

Re: Find value that are lowercase in where clause

ljim1075 — Fri, 22 Oct 2021 18:28:38 GMT

Reeza,

Thank you for your response! I've ran your query and it worked perfectly, but when i run it on my end on my server Teradata table, see results below:

When I look for the particular primary key the I know it has lower case, see response below:

Also when I submit a proc freq query, see response below:

It almost assumes that 'J' and 'j' are the same

There are no errors in the log. Let me know what you think...

TY!

Re: Find value that are lowercase in where clause

Reeza — Fri, 22 Oct 2021 19:00:23 GMT

Teradata can have columns that are casespecific or noncasespecific. Can you check the attributes on that column in Teradata?
I suspect that's the issue.

https://docs.teradata.com/r/1DcoER_KpnGTfgPinRAFUw/G2qHRG2iKwM5d1Ui61MgCA

Re: Find value that are lowercase in where clause

ljim1075 — Tue, 26 Oct 2021 14:46:52 GMT

Reeza,

Based on the info that you've provided, I've found this doc: https://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_913/access_teradata_9298.pdf which clarifies some of the case sensitive issues with Teradata. To solve for 90% of my issue I've added DBSLICEPARM = All into my Libname statement and it worked! Now I say 90% solved, because when I run a PROC FREQ statement it still does not count the lowercase values.

Overall, thank you for the info!