How to combine contains and in a list operator in proc sql?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 99
Accepted Solution

How to combine contains and in a list operator in proc sql?

I encounter a problem, a talbe will contain a variable which is manually inputted, and it will have personalized narrative instead of formal one.

I use

upcase(t1.'Narrative 1'n) not contains 'WAIVE' and upcase(t1.'Narrative 1'n) not contains 'ANNUAL' and upcase(t1.'Narrative 1'n) not contains 'REPLACEMENT' and upcase(t1.'Narrative 1'n) not contains 'DSR' and upcase(t1.'Narrative 1'n) not contains 'TRAFFIC' in proc sql to be the filter which can list all of them, but new ones appear every month.

thus I thought of a new way.

%let forbidden_word="'WAIVE','ANNUAL','DSR'...";

try to use this let to add new fobidden word into every month.and

not contain should combine with not in a list in proc sql where.

can anyone help to give a solution.

Thanks.


Accepted Solutions
Solution
‎05-20-2012 11:20 PM
Respected Advisor
Posts: 4,651

Re: How to combine contains and in a list operator in proc sql?

Assuming you have some ID for the narratives, you could put the forbidden words in a small dataset like this :

data sentences;
length sentence $100;
id = _n_;
input sentence $&;
datalines;
this ia a normal sentence
This is a forbidden word sentence
This sentence has two forbidden words, it should be excluded
;


data forbidden;
length word $20;
input word $&;
word = upcase(word);
datalines;
forbidden word
be excluded
;

proc sql;
create table goodSentences as
select * from sentences where id not in
(select id from sentences inner join forbidden on upcase(sentence) contains trim(word));

Message was edited by: PG to support forbidden expressions instead of just words.

PG

View solution in original post


All Replies
Solution
‎05-20-2012 11:20 PM
Respected Advisor
Posts: 4,651

Re: How to combine contains and in a list operator in proc sql?

Assuming you have some ID for the narratives, you could put the forbidden words in a small dataset like this :

data sentences;
length sentence $100;
id = _n_;
input sentence $&;
datalines;
this ia a normal sentence
This is a forbidden word sentence
This sentence has two forbidden words, it should be excluded
;


data forbidden;
length word $20;
input word $&;
word = upcase(word);
datalines;
forbidden word
be excluded
;

proc sql;
create table goodSentences as
select * from sentences where id not in
(select id from sentences inner join forbidden on upcase(sentence) contains trim(word));

Message was edited by: PG to support forbidden expressions instead of just words.

PG
Frequent Contributor
Posts: 99

Re: How to combine contains and in a list operator in proc sql?

PGStats ,

Thanks for your reply, a Sql in sql should solve this problem.

And thanks for taking time typing those example code for me, very thanks.

Frequent Contributor
Posts: 99

Re: How to combine contains and in a list operator in proc sql?

I didn't make myself clear. that the narrative might be 'waive fee', I just pick out the key word 'waive' thus using contains operator, what in % let forbidden word is just part of the whole sentence in variable narrative.

for eg

Narrative
ANNUAL FEE
TT Fee

I want to keep the records with TT fee, but exclude annual fee,

thus that i use contains instead of eq operator oringinally.

Frequent Contributor
Posts: 99

Re: How to combine contains and in a list operator in proc sql?

Linlin,

     Thank u for your reply indeed.

Respected Advisor
Posts: 4,651

Re: How to combine contains and in a list operator in proc sql?

An alternative that wouldn't require a unique ID would be :

data sentences;
length sentence $100;
input sentence $&;
datalines;
this ia a normal sentence
This is a forbidden word sentence
This sentence has two forbidden words, it should be excluded
;


data forbidden;
length word $20;
input word $&;
word = upcase(word);
datalines;
forbidden word
be excluded
;

proc sql;
create table goodSentences as
select sentences.* from sentences left join
forbidden on upcase(sentence) contains trim(word)
where word is missing;

You might want to compare both methods for efficiency.

PG

PG
Frequent Contributor
Posts: 99

Re: How to combine contains and in a list operator in proc sql?

When I encounter problems, there's always some guy to help. Thus I will give out what I did to solve the problem in this way:

%let forbiddenword= 'WAIVE ANNUAL DSR REPLACEMENT TRAFFIC TAXI INSURANCE';

*TOTAL 7 VARIABLE;

data aa2;

set aa1;

i=1;

condition=0;

do until (i=8);

word=scan(&forbiddenword,i);

word_pre=cats('/',word,'/');

condition=prxmatch(word_PRE, Narrativecombine);

if condition>0 then i=8;

else do;

i=i+1;

end;

end;

IF CONDITION=0;

DROP I word word_pre CONDITION;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 2718 views
  • 4 likes
  • 2 in conversation