topic Re: SQL to exclude records based on index function? in SAS Programming

SQL to exclude records based on index function?

SciFiGuy0 — Fri, 03 Feb 2017 18:33:28 GMT

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

Re: SQL to exclude records based on index function?

ballardw — Fri, 03 Feb 2017 18:42:43 GMT

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;

Re: SQL to exclude records based on index function?

art297 — Sat, 04 Feb 2017 03:43:52 GMT

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

Re: SQL to exclude records based on index function?

Peter_C — Sat, 04 Feb 2017 22:47:26 GMT

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