Desktop productivity for business analysts and programmers

"Contains" function in Enterprise Guide

Reply
Occasional Contributor
Posts: 9

"Contains" function in Enterprise Guide

Hello,

Maybe my question is silly but I have spent several hours trying to solve my problem and it still doesn't work!:smileycry:

I have a list of domains and a list of numbers / numbers-letters combinations. If domain contains any of those numbers or numbers-letters combination, I have to exclude it from the table.

Example:

Forbidden combination: "hotnail";

Thus, domains with typo such as e.g. hotnail.co.uk have to be excluded from the table.

If I use

t1.Domain NOT CONTAINS 'hotnail', then it not only excludes "hotnail" domains but also those which contain HOT in them, such as correct "hotmail" ones.

Which operator should I use in order to exclude only the exact phrase? CONTAINS work in a similar way with LIKE which is not suitable for me.

Thank you very much in advance!

Grand Advisor
Posts: 17,427

Re: "Contains" function is EG

index or find functions?

Super Contributor
Posts: 307

Re: "Contains" function is EG

The CONTAINS operator searches for the string specified, so your expression (t1.Domain NOT CONTAINS 'hotnail') should not be excluding strings containing "hotmail" if it is looking for "hotnail".

You can confirm this simply:

data test ;

input var $7.;

cards;

hotmail

hotnail

hot

hotmall

hotmaii

hotness

;

run;

proc sql;

      create table want as

      select * from test

      where var NOT contains 'hotnail';

;

quit;

Super Contributor
Posts: 307

Re: "Contains" function is EG

Are you using explicit pass-through?

Occasional Contributor
Posts: 9

Re: "Contains" function is EG

Fugue, what is "explicit pass-through"? I am not very experienced in SAS programming. The problem is that my code eliminates strings that are not specified in NOT CONTAINS expressions. I don't know how to solve this problem.

Super Contributor
Posts: 307

Re: "Contains" function is EG

The reason I asked about explicit pass-through is because the CONTAINS operator is SAS specific. It will not work in explicit pass-through.

Super User
Super User
Posts: 6,370

Re: "Contains" function in Enterprise Guide

I suspect that you are making a logic error when trying filter for multiple excluded terms.

If you code (X NE 'Y') or (X NE 'Z') then nothing will be eliminate because for any value of X one of those two conditions is true.

You probably want to code something like this:

NOT (t1.Domain  CONTAINS 'hotnail' or t1.Domain  CONTAINS 'notmail' or .... )

Occasional Contributor
Posts: 9

Re: "Contains" function in Enterprise Guide

Thank you Tom, I have understood what is the problem. First of all I used AND instead of OR. Then corrected the code as you suggested. I have also found out why does the code eliminate strings that don't have to be eliminated. For example, I want to eliminate all emails which prefix is "it". Like it@hotmail.com. But if I state NOT (t1.Prefix CONTAINS 'it' OR...), it will also eliminate 'white', because white has 'IT' in it. How to handle this?

Respected Advisor
Posts: 3,065

Re: "Contains" function in Enterprise Guide

NOT (t1.Domain  CONTAINS 'hotnail' or t1.Domain  CONTAINS 'notmail' or substr(t1.Domain,1,2) = 'it' )

Occasional Contributor
Posts: 9

Re: "Contains" function in Enterprise Guide

Thank you friends, it is now more clear for me how to combine different functions in the code. Thank you for the help! I don't close this topic for some time, maybe new questions will arise.

Occasional Contributor
Posts: 9

Re: "Contains" function in Enterprise Guide

If I want to eliminate all email prefixes that contain only numbers, is it somehow possible to write it in a code? For example, I have

Email

12345@hotmail.com

34958409@hotmail.com

I separate prefixes and get:

Prefix

12345

34958409

Is it possible to declare in a code that if t1Smiley Tonguerefix = numeric, then exclude? I cannot assign numeric type to the whole variable, because it can also contain nominal values and its type is character. t1.Prefix IN ('1', '2', '3', '4', '5', '6', '7', '8', '9') doesn't work.

Thanks.

Super Contributor
Posts: 307

Re: "Contains" function in Enterprise Guide

Have you taken a look at the regular expression functions?

Ask a Question
Discussion stats
  • 11 replies
  • 7748 views
  • 6 likes
  • 5 in conversation