topic Re: Like operator not working in SAS Programming

Like operator not working

Abraham — Fri, 03 Mar 2017 06:38:58 GMT

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

Re: Like operator not working

user24feb — Fri, 03 Mar 2017 07:37:39 GMT

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;

Re: Like operator not working

RW9 — Fri, 03 Mar 2017 09:31:53 GMT

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;

Re: Like operator not working

Ksharp — Fri, 03 Mar 2017 10:23:06 GMT


 
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;

Re: Like operator not working

Abraham — Fri, 03 Mar 2017 14:43:58 GMT

Thank you very much everyone for your support and help