Date:         Fri, 7 Nov 2003 16:35:39 -0800
Reply-To:     "Huang, Ya" <yhuang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <yhuang@AMYLIN.COM>
Subject:      Re: using subselect to expel records
Comments: To: Rune Runnestoe <rune@FASTLANE.NO>
Content-Type: text/plain; charset="iso-8859-1"

Here is my approach, with a little bit help
of data step (n=_n_). If you don't like the data step,
you can use monotonic() to get it with an extra sql.

data forbidden_words;
length not_legal $10.;
input not_legal;
cards;
not
life
vcr
;

data my_table_1;
length text $30;
n=_n_;
input text;
cards;
This_is_my_life
This_is_my_dog
This_is_my_table
This_is_not_me
This_is_my_tv
This_is_not_vcr
;

proc sql;
select distinct a.text
from my_table_1 a, forbidden_words b
where a.text not contains trim(b.not_legal)
group by n
having count(*) = (select count(*) from  forbidden_words)
;
run;
-------------
text
ffffffffffffffff
This_is_my_dog
This_is_my_table
This_is_my_tv


Regards,

Ya Huang


-----Original Message-----
From: Rune Runnestoe [mailto:rune@FASTLANE.NO]
Sent: Friday, November 07, 2003 12:01 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: using subselect to expel records


Here is my code, it can be pasted into the SAS editor and run, In case
it does not seem self-explained, the issue is that I want MY_TABLE_2
to expel records where the column called TEXT in MY_TABLE_1 contains a
string that appears in the column TEXT in another table called
FORBIDDEN_WORDS. The column TEXT may contain more than one word both
in MY_TABLE_1 and FORBIDDEN_WORDS. The problem in a real case I have,
is that I get the error message: "Subquery evaluated to more than one
row". With the result that I get no records in MY_TABLE_2.

data forbidden_words;
length not_legal $10.;
input not_legal;
cards;
not
;


data my_table_1;
length text $30;
input text;
cards;
This_is_my_life
This_is_my_dog
This_is_my_table
This_is_not_me
;

proc sql;
        create table my_table_2 as
                select text
                from my_table_1
                where text not contains
                        (select not_legal
                        from forbidden_words);
run;

Why doesn't the table MY_TABLE_2 expel the record "This_is_not_me" ?
How do I make the code expel it ?

Regards

Rune Runnestoe