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:
customers:
Customer ID# Possessions
------------------ ----------------
1 'apples; oranges'
2 'pears'
3 'pears; cheese'
objects:
Object
---------
'apples'
'pineapples'
'cheese'
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
proc sql;
create table customer_pt as select *
from customers as a, objects as b
where a.Possessions like ('%' + b.Object + '%');
quit;
but that just produces errors...
Any help would be appreciated...