how to replace string

Question

if column 'all' contains the string which is in column 'sch', then this string will be replaced by the string in 'rep'. the column 'new_all' is what I expected.

data a0;
input sch $9. rep $14. ;
cards;
map_clm  map_claim
xyz_ttt  xyz    
drug_clm drug_clm_test 
fee_sch  fee_sch_test
;
run;

data a1;
input all $26. new_all $30.;
cards;
from abc.xyz_ttt d2 left from abc.xyz d2 left
D1.Xwlk,abc.xyz_TTT left D1.xwlk,abc.xyz left
d1.x,abc.map_clms,d2.dos d1.x,abc.map_clms,d2.dos
ABC.XYZ_Ttt left join d1 ABC.xyz left join d1
,tt.drug_CLM, tt.Xyz_ttt ,tt.drug_clm_test, tt.xyz
d3.DOS,t2.fee_SCH,tt.day fd3.DOS,t2.fee_sch_test,tt.day
;
run;

Answer 1

I'm assuming that you want to transform column all into column new_all, using the values from the a0 data set to describe/control the transformation.

%macro do_it;

* Set up some macro variable arrays to hold the from/to pairs;
data _null_;
  set a0 end=end;
  call symput("sch" || _n_, sch);
  call symput("rep" || _n_, rep);
  if end then call symput("max", _n_);
run;

* Process the data;
data want;
  length new_all_new $ 200;
  set a1;

  * Copy the incoming value to the output;
  new_all_new = all;

  * Apply each of the transformations in turn;
  %do i = 1 %to &max;
    new_all_new = tranwrd(new_all_new, "&&sch&i", "&&rep&i");
  %end;

  * Did we get the expected value?;
  if new_all_new ne new_all then do;
    put "ERROR: Did not get expected value: " all= new_all= new_all_new=;
  end;

run;

%mend;

%do_it;

The above code should be pretty close, though I'm unable to test it at the moment. This code is case-sensitive; your expected data suggests that you want to apply the transformations case-insensitively, whilst also preserving the case of the rest of the string. That's a little trickier to implement; if it's really a requirement, a regex search and replace (prxparse("s/&&sch&i/&&rep&i/");) might be the best approach.

There's also the issue that you want to replace 'map_clm' but not 'map_clms', which also suggests regexes might be the cleanest solution.

In any case, this code gives a reasonable starting point for you to work from, I think. It's great that you have built-in expected values to test against.

Are you really modifying SQL code programatically?