BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Abraham
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

 
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

4 REPLIES 4
user24feb
Barite | Level 11

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26
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;
Ksharp
Super User

 
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;


Abraham
Obsidian | Level 7

Thank you very much everyone for your support and help

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
  • 1667 views
  • 3 likes
  • 4 in conversation