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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.