topic Re: Generate a list in subquery in SAS Programming

Generate a list in subquery

kaziumair — Thu, 01 Apr 2021 09:15:29 GMT

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.

Re: Generate a list in subquery

SASKiwi — Wed, 31 Mar 2021 07:11:37 GMT

Try this:

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

Re: Generate a list in subquery

kaziumair — Wed, 31 Mar 2021 07:52:59 GMT

Its giving an error

Re: Generate a list in subquery

Kurt_Bremser — Wed, 31 Mar 2021 07:56:14 GMT

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.

Re: Generate a list in subquery

SASKiwi — Wed, 31 Mar 2021 07:58:18 GMT

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;

Re: Generate a list in subquery

Ksharp — Wed, 31 Mar 2021 12:01:35 GMT

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;

Re: Generate a list in subquery

DaanDNR — Wed, 31 Mar 2021 12:05:29 GMT

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;

Re: Generate a list in subquery

kaziumair — Thu, 01 Apr 2021 09:16:11 GMT

I have added sas code for example data

Re: Generate a list in subquery

Kurt_Bremser — Thu, 01 Apr 2021 09:44:28 GMT

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;

Re: Generate a list in subquery

DaanDNR — Thu, 01 Apr 2021 10:05:27 GMT

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;

Re: Generate a list in subquery

DaanDNR — Thu, 01 Apr 2021 10:09:13 GMT

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.