topic Re: Like operator not working during pattern matching in SAS Programming

Like operator not working during pattern matching

Abraham — Wed, 08 Nov 2017 18:32:58 GMT

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;

Re: Like operator not working during pattern matching

Yavuz — Wed, 08 Nov 2017 19:38:49 GMT

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;

Re: Like operator not working during pattern matching

ChrisNZ — Wed, 08 Nov 2017 22:58:34 GMT

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.

Re: Like operator not working during pattern matching

SuryaKiran — Wed, 08 Nov 2017 23:19:17 GMT

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.

Re: Like operator not working during pattern matching

Tom — Wed, 08 Nov 2017 23:55:07 GMT

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.