Generate a list in subquery

[kaziumair]

data headlines;
	input story & $200.;
	datalines;
	The president will have to resign for being involved in a political scandal
	The grand jury has handed down indictments against several mobsters
	This is another headline
	;
run;
data terms;
	length word $ 20;
	input word $;
	datalines;
	scandal
	indict
	abuse
	;
run;

Hi , I have two tables . 

1) Table A consists a list of news headlines

2) Table B consists a list of terms 

I want to create a query that select headlines containing terms from a list of terms .

This is the query I used , I am getting an error saying subquery evaluated to more than one row.

proc sql;
select *
from work.stories
where titles contains(select term
from example.searchterms);
quit;

I want the subquery to generate a list of terms and the outer query to produce headlines containing those terms.

[SASKiwi]

Try this:

proc sql;
select *
from work.stories
where titles exists in (select term
from example.searchterms);
quit;

[kaziumair]

Its giving an error

[SASKiwi]

Alternatively and more efficient:

proc sql;
select *
from work.stories as A
where titles in (select B.term
from example.searchterms as B
where A.titles = B.term);
quit;

[Kurt_Bremser]

Please supply example data in usable form (data steps with datalines).

If code throws an ERROR or WARNING, post the log. Use this button for posting logs:

The "little running man" right next to it is for posting SAS code, particularly your DATALINES code.

[kaziumair]

I have added sas code for example data

[Kurt_Bremser · Accepted solution]

Try this:

data headlines;
input story & $200.;
datalines;
The president will have to resign for being involved in a political scandal
The grand jury has handed down indictments against several mobsters
This is another headline
;

data terms;
length word $ 20;
input word $;
datalines;
scandal
indict
abuse
;

proc sql noprint;
select nobs into :nobs
from dictionary.tables
where libname = "WORK" and memname = "TERMS";
quit;

data want;
set headlines;
if _n_ = 1
then do;
  array terms {&nobs.} $20 _temporary_;
  do i = 1 to &nobs;
    set terms;
    terms{i} = word;
  end;
end;
do i = 1 to &nobs. until (flag);
  flag = find(story,trim(terms{i}));
end;
if flag;
word = terms{i};
drop flag i;
run;

[Ksharp]

You should use cartesian product.

proc sql;
select *
from work.stories as a , example.searchterms as b
where a.titles contains strip(b.term );
quit;

[DaanDNR]

Something like this?

data A;
headline = 'How to do this';
output;
headline = 'How not to do this';
output;
run;

data B;
term = 'not';
output;
term = 'applicable';
output;
run;

proc sql;
select distinct headline
from A inner join B
on A.headline contains B.term
;
quit;

[DaanDNR]

proc sql;
select distinct story
from headlines 
where exists (select 'X' from terms where headlines.story contains kstrip(terms.word) and terms.word in ('indict'))
;
quit;

* Alternatively dd mutual variable to prevent cartesian product;
data headlines;
  input key story & $200.;
  datalines;
  1 The president will have to resign for being involved in a political scandal
  1 The grand jury has handed down indictments against several mobsters
  1 This is another headline
  ;
run;

data terms;
  input key word $20.;
  datalines;
  1 scandal
  1 indict
  1 abuse
  ;
run;

proc sql;
  select distinct story
  from headlines inner join terms
  on headlines.key = terms.key
  where headlines.story contains kstrip(terms.word)
  and kstrip(terms.word) in ('indict')
  ;
quit;

[DaanDNR]

Sorry the layout is somewhat scrambled. What I meant to say is that you can use the SQL statement using exists

proc sql;
select distinct story
from headlines
where exists (select 'X' from terms where headlines.story contains kstrip(terms.word) and terms.word in ('indict'))
;
quit;

or alternatively add a mutual variable to the datasets to prevent the cartesian product.