select with a where clause in a SAS

[GN0001]

Proc sql;

create table mytestTable as
select distinct tablename.variableName
from libName.tablename
where tablename.variablename = upcase('Group ABC');

Quit;

This code doesn't return any values, however, we have values for this variable in the table. Also, log says no error.

Any help is greatly appreciated.

Regards,

blue blue

[sbxkoenk]

Hello,

Are you sure you have values = 'GROUP ABC' (in uppercase) in the variable 'VariableName'?

Maybe try this case-insensitive comparison:
(and strip function is removing leading and trailing blanks)

where strip(upcase(tablename.variablename)) = 'GROUP ABC';

Koen

[Rydhm]

You can try to use like operator

where upcase(variable_name) like upcase('%Group ABC%');

[ballardw · Accepted solution]

It is going to come down to actual content of your data in this case assuming you haven't managed to hide a detail by removing actual library, data set and variable names.

Equality with character variables is a very strong requirement. Any  minor spelling difference other than trailing blanks will make equality not true. So if your value is "GroupABC" "group ABC" "Group Abc" or harder to spot " Group ABC" they are not equal (lots more variations). For many cases, just to get around minor case differences we will use something like:

where upcase(variable) = "GROUP ABC" or similar.

This returns no records because the variable is not forced to the same case as the comparison

data junk;
   infile datalines missover;
   input var $10.;
datalines;
Group ABC
 Group ABC
group abc
group Abc
;

proc sql;
   create table testtable as
   select distinct var
   from junk
   where var = upcase('Group ABC');

Quit;

This may be more like what you expected:

proc sql;
   create table testtable2 as
   select distinct var
   from junk
   where upcase(var) = upcase('Group ABC');

Quit;

but still does not find the second value because it has a leading space.

Which could be addressed with

proc sql;
   create table testtable3 as
   select distinct var
   from junk
   where upcase(strip(var)) = upcase('Group ABC');

Quit;

but only 3 in the output because the spelling after striping the leading blank matches the first record.

Equality is evaluated character by character from left to right and as soon as something is not equal SAS returns not equal for the comparison.

Other things that could be "not equal" is a character in a unicode set that looks like "G" but because the underlying coding is different it is not equal to the "G" you type in programming.