I have been trying to think of a solution to this for awhile, but I have had no luck. I have a list of customers (with Customer ID and Possessions, which is a semi-colon delimited list as a string) and a list of objects (as strings), as follows:
I want to find all customers whose Possessions can also be found in the list of Objects (only customers 1 and 3 in this case, since the first has 'apples' whereas the third has 'cheese'. Customer 2 has nothing which is in the list of objects). What is the best way to do this in SAS?
I have tried using a SQL statement that looks like
create table customer_pt as select *
from customers as a, objects as b
where a.Possessions like ('%' + b.Object + '%');
I am certain that there are many ways to do this. On way I would try is first to split up your Possessions column so you will have each possession in separate columns, then transpose that table, so that you will have just one possession per row. After doing that, just do an inner join to the Objects table.
One additional confounding problem with this is that I want to also detect partial matches. Namely, if one customer has "cheese slices" for Possessions then I want that to also be flagged. I can't imagine it being possible to do this any other way than per-row string searching of some kind.