topic Re: Remove the matching string form another column in SAS Programming

Remove the matching string form another column

Sathish_jammy — Tue, 09 Mar 2021 08:46:33 GMT

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.

Re: Remove the matching string form another column

Kurt_Bremser — Tue, 09 Mar 2021 09:37:46 GMT

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.

Re: Remove the matching string form another column

yabwon — Tue, 09 Mar 2021 09:41:18 GMT

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

Re: Remove the matching string form another column

Ksharp — Tue, 09 Mar 2021 11:13:04 GMT

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;