Remove the matching string form another column

[Sathish_jammy]

Dear Experts,

I need to remove the matched string form one column to another. Kindly go through the given sample

Pv_Drugs	Fv_Drugs	Effect
Insu	Met,Sulp,SGLT	Met,Sulp,SGLT
Insu	Sulp	Sulp
Insu,DPP	Met	Met
Insu,DPP	Sulp,DPP,Thiaz	Sulp, Thiaz
Met	Insu,Met	Insu
Met	Met,Sulp,Thiaz	Sulp
Met,Sulp	Met,Sulp,AGI	AGI
Sulp,AGI	Met,AGI	Met
Sulp,DPP	Met,Sulp,SGLT	Met, Sulp, SGLT
Thiaz	Met,Sulp,AGI	Met, Sulp, AGI
AGI	Insu,Met,Sulp	Insu,Met,Sulp

The Effect column is my expected result. I have to filter out the Pv_drugs values in Fv_drugs column.

Consider the Pv_drugs as Base drug and Fv_drugs as Folloup drug. Fv_drugs is the major constrain.

[Kurt_Bremser]

Use nested do loops:

data have;
infile datalines dlm=";" dsd truncover;
input (Pv_Drugs Fv_Drugs) (:$30.);
datalines4;
Insu;Met,Sulp,SGLT
Insu;Sulp
Insu,DPP;Met
Insu,DPP;Sulp,DPP,Thiaz
Met;Insu,Met
Met;Met,Sulp,Thiaz
Met,Sulp;Met,Sulp,AGI
Sulp,AGI;Met,AGI
Sulp,DPP;Met,Sulp,SGLT
Thiaz;Met,Sulp,AGI
AGI;Insu,Met,Sulp
;;;;

data want;
set have;
length effect $30;
do i1 = 1 to countw(fv_drugs,",");
  flag = 1;
  do i2 = 1 to countw(pv_drugs,",");
    if scan(fv_drugs,i1,",") = scan(pv_drugs,i2,",") then flag = 0;
  end;
  if flag then effect = catx(",",effect,scan(fv_drugs,i1,","));
end;
drop i1 i2 flag;
run;

Please note how the example dataset is presented in a data step with datalines. Always present your own data like this, as it makes it much easier for those intended to help you to recreate your dataset. See it as a basic courtesy.

Using datalines to create data is a valuable skill in itself and not complicated. Even idiots like myself have mastered it.

[yabwon]

think this should work:

data have;
input 
(Pv_Drugs Fv_Drugs) (: $ 20.);
cards;
Insu	Met,Sulp,SGLT	
Insu	Sulp	
Insu,DPP	Met	
Insu,DPP	Sulp,DPP,Thiaz	
Met	Insu,Met	
Met	Met,Sulp,Thiaz	
Met,Sulp	Met,Sulp,AGI	
Sulp,AGI	Met,AGI	
Sulp,DPP	Met,Sulp,SGLT	
Thiaz	Met,Sulp,AGI	
AGI	Insu,Met,Sulp
;
run;

data want;
  set have;

  Effect = Fv_Drugs;
  do _N_ = 1 to countw(Pv_Drugs,",");
    Effect = TRANWRD(Effect, strip(scan(Pv_Drugs, _N_, ",")), "");
  end;

  Effect = TRANSLATE(strip(compbl(TRANSLATE(Effect, " ", ","))), ",", " ");
run;
proc print;
run;

Bart

[Ksharp]

data have;
infile datalines dlm=";" dsd truncover;
input (Pv_Drugs Fv_Drugs) (:$30.);
datalines4;
Insu;Met,Sulp,SGLT
Insu;Sulp
Insu,DPP;Met
Insu,DPP;Sulp,DPP,Thiaz
Met;Insu,Met
Met;Met,Sulp,Thiaz
Met,Sulp;Met,Sulp,AGI
Sulp,AGI;Met,AGI
Sulp,DPP;Met,Sulp,SGLT
Thiaz;Met,Sulp,AGI
AGI;Insu,Met,Sulp
;;;;

data want;
set have;
length effect $300;
do i = 1 to countw(fv_drugs,", ");
  temp=scan(fv_drugs,i,',');
  if not findw(Pv_Drugs,strip(temp),', ') then effect=catx(',',effect,temp);
end;
drop i temp;
run;