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;
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.