topic Re: FIND Function FIND() not giving expected result in SAS Programming

FIND Function FIND() not giving expected result

vkabdwal — Tue, 07 Dec 2021 07:35:50 GMT

Find function is giving unexpected results.

Output:

I am expecting 1 as the answer here but getting 0.

In text format output for last three fields:
full_rating: "Action: New Rating (Mar-07-2018)
From ""-"" to ""BB-"""

b_rating: "Action: New Rating (Mar-07-2018)
From ""-"" to ""BB-"""

find: 0

Code:

proc sql;
create table old_match_change as

select *, find(compress(upcase(full_rating),' ():"'), compress(upcase(b_rating),' ():"')) as find
from old_match 
where find(compress(upcase(full_rating),' ():"'), compress(upcase(b_rating),' ():"'))=0;
quit;

Re: FIND Function FIND() not giving expected result

ChrisNZ — Tue, 07 Dec 2021 07:46:48 GMT

You seem to have a line break. Compress non-printable characters too a lines breaks can take different forms.

Re: FIND Function FIND() not giving expected result

vkabdwal — Tue, 07 Dec 2021 07:48:46 GMT

What solution you suggest? Please elaborate a bit.

Re: FIND Function FIND() not giving expected result

ChrisNZ — Tue, 07 Dec 2021 07:52:15 GMT

Compare the lengths of the compressed strings. If they are the same, compare their value printed with the $hex. format.

Re: FIND Function FIND() not giving expected result

ChrisNZ — Tue, 07 Dec 2021 07:54:43 GMT

The compress function allows you to eliminate non-printable characters. See the documentation.

Note also that the find function allows case insensitive comparisons, so you needn't use the upcase function here. Again see the documentation.

Re: FIND Function FIND() not giving expected result

s_lassen — Tue, 07 Dec 2021 09:17:58 GMT

If you want to get rid of the line breaks, use 'c' (remove control characters) as the third parameter to COMPRESS, e.g.:

proc sql;
create table old_match_change as

select *, find(compress(upcase(full_rating),' ():"','c'), compress(upcase(b_rating),' ():"','c')) as find
from old_match 
where find(compress(upcase(full_rating),' ():"','c'), compress(upcase(b_rating),' ():"','c'))=0;
quit;

Re: FIND Function FIND() not giving expected result

vkabdwal — Tue, 07 Dec 2021 09:31:19 GMT

Did the trick. Thanks @s_lassen @ChrisNZ for you valuable inputs.