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 |
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;
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;
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;
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;
Thank you very much everyone for your support and help
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.