topic "Contains" function in Enterprise Guide in SAS Enterprise Guide

"Contains" function in Enterprise Guide

Dataminer — Mon, 20 Jan 2014 16:56:19 GMT

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!

Re: "Contains" function is EG

Reeza — Mon, 20 Jan 2014 17:28:15 GMT

index or find functions?

Re: "Contains" function is EG

Fugue — Mon, 20 Jan 2014 19:08:22 GMT

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;

Re: "Contains" function is EG

Fugue — Mon, 20 Jan 2014 19:09:42 GMT

Are you using explicit pass-through?

Re: "Contains" function is EG

Dataminer — Sun, 26 Jan 2014 17:44:28 GMT

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.

Re: "Contains" function in Enterprise Guide

Tom — Sun, 26 Jan 2014 21:21:55 GMT

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 .... )

Re: "Contains" function in Enterprise Guide

Dataminer — Mon, 27 Jan 2014 07:59:15 GMT

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?

Re: "Contains" function in Enterprise Guide

SASKiwi — Mon, 27 Jan 2014 18:50:16 GMT

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

Re: "Contains" function is EG

Fugue — Mon, 27 Jan 2014 19:06:32 GMT

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

Re: "Contains" function in Enterprise Guide

Dataminer — Tue, 28 Jan 2014 08:51:04 GMT

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.

Re: "Contains" function in Enterprise Guide

Dataminer — Thu, 30 Jan 2014 08:22:39 GMT

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

12345@hotmail.com

34958409@hotmail.com

I separate prefixes and get:

Prefix

12345

34958409

Is it possible to declare in a code that if t1:Prefix = 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.

Re: "Contains" function in Enterprise Guide

Fugue — Thu, 30 Jan 2014 18:39:20 GMT

Have you taken a look at the regular expression functions?