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;

Your question is not clear, please reformat-reframe it for better clarity — salsinga, Feb 03 '20 at 15:15
Are you asking how to modify the values of ALL so that any reference to the word contained in SCH is replaced with the word in REP? — Tom, Feb 03 '20 at 15:40
Please show your expected output given your input. How is it not just `sch=rep;` for replacement? — Reeza, Feb 03 '20 at 15:51
Tom, yes, if 'all' contains string in 'sch', then use string in 'rep' to replace this string. — Jeff, Feb 03 '20 at 16:54

score 1 · Accepted Answer · answered Feb 04 '20 at 10:10

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?

i used prxchange(s/&&sch&i/&&rep&i/,-1,all), and works well. thanks Chris Long! — Jeff, Feb 06 '20 at 19:32
No problem. Please go ahead and mark this as your accepted answer, if you agree that it should be. — Chris Long, Feb 07 '20 at 09:01

score 1 · Answer 2 · answered Feb 05 '20 at 12:26

1

case when t1.all CONTAINS  t1.sch
then t1.rep
end

If you skip 'else' statement, you'll get missing values (null) for new column if the condition is not satisfied. if you want to keep t1.all values, you have to add else t1.all statement before end.

answered Feb 05 '20 at 12:26

Niqua

386
2
15

you are right, his code is just a draft, lots of work needed. Thanks! – Jeff Feb 06 '20 at 19:33

how to replace string

2 Answers2