topic Re: Using PROC SQL Filter Out data set Rows, searching one variable, that contain terms to be exclud in SAS Programming

Using PROC SQL Filter Out data set Rows, searching one variable, that contain terms to be excluded

rmacarthur — Tue, 08 Sep 2020 16:25:21 GMT

Hi SAS Friends,

Would appreciate some advice on using PROC SQL to filter out a list of excluded terms from a large dataset, am using SAS 9.4

In the example below, the data set COLORS contains the terms to exclude, under the variable "color".

The data set Storys contains the variable "Fruit_Storys". If a term from "color" is found in "Fruit_Storys", need to exclude that row.

So in this example, the only rows that would remain after filtering :

5 sour grey raisins are never chosen
6 sour orange raisins are sometimes chosen

Example is :

data COLORS; 
input color $20. ; 
datalines;
red delicious
purple parsimmons 
green and
blue gala
;

data Storys ;
input ID $2. Fruit_Storys $64.;
datalines ;
1 red delicious apples are the prode of NY 
2 the sweet purple parsimmons are too ripe 
3 yellow and blue gala apples are superb 
4 granny apples are green and lean 
5 sour grey raisins are never chosen 
6 sour orange raisins are sometimes chosen 
;

I know this is straight forward for many of you, and hope to have that level of fluency, some day,

greatly appreciate your guidance,

Thank you !

Re: Using PROC SQL Filter Out data set Rows, searching one variable, that contain terms to be exclud

novinosrin — Tue, 08 Sep 2020 17:15:29 GMT

data COLORS; 
input color $20. ; 
datalines;
red delicious
purple parsimmons 
green and
blue gala
;

data Storys ;
input ID $2. Fruit_Storys $64.;
datalines ;
1 red delicious apples are the prode of NY 
2 the sweet purple parsimmons are too ripe 
3 yellow and blue gala apples are superb 
4 granny apples are green and lean 
5 sour grey raisins are never chosen 
6 sour orange raisins are sometimes chosen 
;

proc sql;
 create table want as
 select distinct id, Fruit_Storys
 from storys, colors
 group by id
 having not max(find(Fruit_Storys,strip(color)));
quit;

Re: Using PROC SQL Filter Out data set Rows, searching one variable, that contain terms to be exclud

PGStats — Tue, 08 Sep 2020 17:42:28 GMT

proc sql;
select *
from storys as a
where not exists (select * from colors where a.fruit_storys contains trim(color));
quit;

Re: Using PROC SQL Filter Out data set Rows, searching one variable, that contain terms to be exclud

rmacarthur — Tue, 08 Sep 2020 19:45:49 GMT

Thanks, both relies worked, and chose the former mainly bc it was easier to incorporate into my code , much appreciated !