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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.