DATA Step, Macro, Functions and more

Like operator not working

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

Like operator not working

Hi,

From the below two datasets, I want to search the string 'adv_event' from child dataset in clob data type variable 'details' from master table. I wrote the below program but it is not working or Is there any alternative method.

 

 

data Master;
input pid details & : $100.;
cards;

101 The new NASA Software Catalogue includes the code LEWICE, developed to help study
102 core module will be followed by a series of launches for other components of the space
103 Pateinst suffered with ASTHMA
104 Surgery is the best way to remove tumor from Pain
105 Trial need patient with heart burn earlier
106 Clinical trial do not require volunteer with allergicasthmA.
;
run;

data event;
input adv_event $15.;
cards;
death
asthma
heart burn
;
run;

proc sql;
create table final as
select pid from master
where upper(compress(details)) like "%"||(select compress(upper(adv_event)) from event)||"%";
quit;

 

 

My output

pid adv_event
103 asthma
105 heart burn 
106 asthma

Accepted Solutions
Solution
‎03-03-2017 09:44 AM
Super User
Posts: 9,687

Re: Like operator not working


 
data Master;
input pid details & : $100.;
cards;
101 The new NASA Software Catalogue includes the code LEWICE, developed to help study
102 core module will be followed by a series of launches for other components of the space
103 Pateinst suffered with ASTHMA
104 Surgery is the best way to remove tumor from Pain
105 Trial need patient with heart burn earlier
106 Clinical trial do not require volunteer with allergicasthmA.
;
run;
data event;
input adv_event $15.;
cards;
death
asthma
heart burn 
;
run;
proc sql;
create table final as
select pid from master as a,event as b
where upper(compress(details)) contains compress(upper(adv_event));
quit;


View solution in original post


All Replies
Super Contributor
Posts: 336

Re: Like operator not working

I would use a hash iterator:

 

data Master;
input pid details & : $100.;
cards;
101 The new NASA Software Catalogue includes the code LEWICE, developed to help study
102 core module will be followed by a series of launches for other components of the space
103 Pateinst suffered with ASTHMA
104 Surgery is the best way to remove tumor from Pain
105 Trial need patient with heart burn earlier
106 Clinical trial do not require volunteer with allergicasthmA.
;
run;

data event;
input adv_event $15.;
cards;
death
asthma
heart burn 
;
run;

Data want_hash (Drop=rc);
  If _N_ eq 1 Then Do;
    Declare Hash H (Dataset:'event');
	Declare HIter HI ('H');
	H.Definekey('adv_event');
	H.Definedata('adv_event');
	H.Definedone();
	If 0 Then Set event;
  End;
  Set Master;
  rc=HI.First();
  Do While (not rc);
    If Find(UpCase(details),UpCase(Trim(adv_event))) Then Do;
      Output;
	  Leave;
	End;
	rc=HI.Next();
  End;
Run;
Super User
Super User
Posts: 7,415

Re: Like operator not working

data _null_;
  set event end=last;
  if _n_=1 then call execute('data want; set master; length adv_event $200;');
  call execute(cats('if index(upcase(details),"',upcase(adv_event),'") > 0 then adv_event="',adv_event,'";'));
  if last then call execute('run;');
run;

The above will generate a datastep containing an if for eah obs in event.  Questions however, what happens if more than one appears in a row, what about part of a word, e.g. ASTHMAtic?

You can also use merging:

proc sql;
  create table FINAL as
  select A.PID,
           B.ADV_EVENT 
  from   MASTER A
  left join EVENT B
  on      index(upcase(A.DETAILS),upcase(B.ADV_EVENT)) > 0;
quit;
Solution
‎03-03-2017 09:44 AM
Super User
Posts: 9,687

Re: Like operator not working


 
data Master;
input pid details & : $100.;
cards;
101 The new NASA Software Catalogue includes the code LEWICE, developed to help study
102 core module will be followed by a series of launches for other components of the space
103 Pateinst suffered with ASTHMA
104 Surgery is the best way to remove tumor from Pain
105 Trial need patient with heart burn earlier
106 Clinical trial do not require volunteer with allergicasthmA.
;
run;
data event;
input adv_event $15.;
cards;
death
asthma
heart burn 
;
run;
proc sql;
create table final as
select pid from master as a,event as b
where upper(compress(details)) contains compress(upper(adv_event));
quit;


Contributor
Posts: 54

Re: Like operator not working

Thank you very much everyone for your support and help

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 181 views
  • 3 likes
  • 4 in conversation