SQL to exclude records based on index function?

Reply
Occasional Contributor
Posts: 18

SQL to exclude records based on index function?

I've got a table A with a field named  Software.  Table B has a field called Exclude.  I'd like to chose all the records from A where the words to exclude are not part of the Software name.   For example:

 

Table A

Software

Microsoft Office Visio

Microsoft Office Word

Adobe Photoshop

Internet Explorer

 

Table B

Exclude

Office

House

Barn

 

Would produce:

 

Table C

Software

Adobe Photoshop

Internet Explorer

Super User
Posts: 11,134

Re: SQL to exclude records based on index function?

This should do what you want for your example

 

proc sql;
   create table TableC as
   select distinct tableA.*
   from TableA, TableB
   where indexw(upcase(table.Software),upcase(TableB.Exclude))=0;
quit;
 
PROC Star
Posts: 7,436

Re: SQL to exclude records based on index function?

I think this might work better:

 

proc sql noprint;
  select Exclude into :excludes separated by '|'
    from TableB
  ;
quit;

data tableC;
  set tableA;
  if prxmatch("m/&excludes./oi",software) = 0;
run;

HTH,

Art, CEO, AnalystFinder.com

 

Valued Guide
Posts: 2,177

Re: SQL to exclude records based on index function?

Havr you tried this kind of sql?

Select a.*
From a left join b
On a.software contains trim( b.exclude)
Where b.exclude is null
Ask a Question
Discussion stats
  • 3 replies
  • 153 views
  • 2 likes
  • 4 in conversation