BookmarkSubscribeRSS Feed
Abraham
Obsidian | Level 7

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;

 

4 REPLIES 4
Yavuz
Quartz | Level 8
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
Tourmaline | Level 20

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
Meteorite | Level 14

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. 

 

Thanks,
Suryakiran
Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 997 views
  • 0 likes
  • 5 in conversation