SQL to exclude records based on index function?

[SciFiGuy0]

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

[ballardw]

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;
 

[art297]

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

[Peter_C]

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