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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.