Cartesian product not optomized

[Batman]

This sql query seems to give me the desired output, but it also note below ("one or more Cartesian product joins that can
not be optimized") makes me wonder if something is amiss.   Is there a fix to the code that will not generate the note?

27 /*Assign Drug Categories where possible to Ingredients not
28 associated with a drug category*/
29 PROC SQL;
30 Create Table FDA_RESOLVED_Process as
31 SELECT FDA.Active_Ingredient
32 ,FDA.Initial_Posting_Date
33 ,FDA.Resolved_Date
34 /*if there is a match, put drug_category, if not use "Not on List"*/
35 , coalescec(EML.Drug_Category, 'Not in List') as Drug_Category
36 /*Ingredient list*/
37 FROM Fda_resolved_data_WO_Blank as FDA
38 /*Drug Category and Name list*/
39 LEFT JOIN Essential_medicines_list as EML
40 /*select if drug name from Essential Medicines is in Active Ingredient*/
41 ON findw(FDA.Active_Ingredient, trim(EML.DRUG_NAME_))
42 ;
NOTE: The execution of this query involves performing one or more Cartesian product joins that can
not be optimized.
NOTE: Table WORK.FDA_RESOLVED_PROCESS created, with 129 rows and 4 columns.

43 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

[Stu_SAS · Accepted solution]

The reason for this is your use of joining with the findw function. Let's look at a simple example that replicates this:

data bmw;
    set sashelp.cars;
    where make = 'BMW';
    drop horsepower;
run;

data bmw_hp;
    set sashelp.cars;
    where make = 'BMW';
    keep make model horsepower;
run;

proc sql;
    create table test as
        select t1.make, t1.model, t2.horsepower
        from bmw as t1
        LEFT JOIN
             bmw_hp as t2 
        ON findw(t1.model, trim(t2.model))
    ;
quit;

For small tables this generally isn't a problem, but for big tables it can be a big issue. If your table is always going to be small then I wouldn't worry about it. 

To get rid of this message you'll need to re-design your join, but since you're taking a variable from one table and checking if it has a word from another table for every row, I don't know if there is a great way to make this more efficient. I can think of ways to do this with, say, a hash table, but you're still doing a lot of looping to pull down every value from the lookup table to feed it into findw(), and it might even be less efficient than SQL.