topic Re: select with a where clause in a SAS in SAS Programming

select with a where clause in a SAS

GN0001 — Mon, 19 Jul 2021 22:35:05 GMT

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

Re: select with a where clause in a SAS

sbxkoenk — Mon, 19 Jul 2021 23:06:14 GMT

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

Re: select with a where clause in a SAS

Rydhm — Mon, 19 Jul 2021 23:15:32 GMT

You can try to use like operator

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

Re: select with a where clause in a SAS

ballardw — Mon, 19 Jul 2021 23:18:12 GMT

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.