Like operator not working during pattern matching

[Abraham]

Hi,

I want to find data of child table when its product is matching with Med_product variable from Master dataset and vice versa.

I tried using below code but I am getting error.

Can you please help me 

data Master;
input Med_product $15.;
cards;
Metformin-oral
TolazolineK
OXALIPLATINSYRUP
METHOTREXATETAB
CISPLATIN
AZATHIOPRINE
;
run;


data child;
input pid product $15.;
cards;
101 Metformin
102 Tolazolines
107 OXALIPLATINS
103 METHOTREXATETAB
104 AZATHMYCIN
105 METHOTREXATE
106 CISPLATIN LIVI
;
run;

proc sql;
create table final as
select c.*
from child c, Master m where (upper(c.product) like '%' || upper(m.Med_product) || '%'
                            or upper(p.product) like '%' || upper(c.Med_product) || '%');
quit;

proc sql;
create table final as
select c.*
from child c where upper(c.product) like '%' || (select distinct upper(m.Med_product) from Master m) || '%';
quit;

[Yavuz]

proc sql;
select catx(" | ",a.product,b.Med_product) as variab
from master a left join child b
on strip(upcase(med_product)) contains strip(upcase(product));
quit;

[ChrisNZ]

Like this?

proc sql;
create table final as
select c.*
from child c, Master m where (upper(c.product) like '%' || upper(strip(m.Med_product)) || '%'
                       or upper(m.Med_product) like '%' || upper(strip(c.product))     || '%');
quit;

You have to remove the trailing spaces. I'd use the contains operator though as @Yavuz showed.

[SuryaKiran]

Hi,

If your trying for a fuzzy match on the name then SPEDIS function is very helpful. 

proc sql;
create table final as
select *
from child c, Master
where find(product,Med_product,'i')>0 or find(Med_product,product,'i')>0;
quit;
proc sql;
create table final as
select *
from child c, Master
where SPEDIS(product,Med_product)<10 or SPEDIS(Med_product,product)<10;
quit;

Here 10 is the score for the fuzzy match I'm looking for. Go to SPEDIS function documentation to know more about it. 

[Tom]

Don't include the trailing spaces in your search pattern.

where upper(c.product) like '%' || upper(trim(m.Med_product)) || '%'
   or upper(p.product) like '%' || upper(trim(c.Med_product)) || '%'

Or better still just use the FIND() function.

where find(c.product,m.Med_product,'it')
   or find(p.product,c.Med_product,'it')

Also note that the UPPER() function is only available in SQL code. The normal SAS function of UPCASE() can be used anywhere in SAS code.