Updating a column by replacing the middle of a string with a different portion of the string

[AshJuri]

data subject_data;
input SUBID;
datalines;                                         /*OUTPUT*/

H7891133 1029 10151024               /*H7891133 1015 10151024*/
H7891133 1029 10241005               /*H7891133 1024 10241005*/
H7891133 1029 10241019               /*H7891133 1024 10241019*/
;
run;

%macro mnemonic(in,out);
when ("&in") substr(subid,1,60) = "&out";
%mend;

data update_sub;
set subject_data;
select (substr(subid,1,60));
  %mnemonic(H7891133 1029 10151024 ,H7891133 1015 10151024)
  %mnemonic(H7891133 1029 10241005 ,H7891133 1024 10241005)
  %mnemonic(H7891133 1029 10241019 ,H7891133 1024 10241019)
  otherwise;
end;
run;

Hi, my code currently utilizes a macro to update a column by replacing the middle of a string with a different portion of the string. For example, in "H7891133 1029 10151024", the middle portion "1029" gets replaced by the first four digits of the last portion of the string "1015" so that the updated string is "H7891133 1015 10151024". However, this requires me to list out the subject numbers within the macro. I would like to efficiently update the entire column without having to do any manual listing of the individual subject numbers as there are many records in the dataset. Would concatenation be an option? Any solutions would be appreciated. 

[Shmuel]

Assuming the substring to be replaced is preceded by a space and followed by a space you don't need a macro, you can use TRANWRD() function.

      variable = tranwrd(variable, "current value", "new_value");

[ballardw · Accepted solution]

First thing is to make sure that your data step runs.

This works for your example:

data subject_data;
input SUBID $ 1-22;
datalines;                                         /*OUTPUT*/
H7891133 1029 10151024               /*H7891133 1015 10151024*/
H7891133 1029 10241005               /*H7891133 1024 10241005*/
H7891133 1029 10241019               /*H7891133 1024 10241019*/
;
run;
data want;
  set subject_data;
  subid = tranwrd(subid,'1029',substr(scan(subid,3),1,4));
run;

However I am concerned that this may not be sufficient. Your select is explicitly using a 60 character string but the values you show to manipulate are only 22. So fixed positions in SUBSTR may not be correct.

Another potential issue is if 1029 appears in more than one position in the value such as "H7810293 1029 10241019" or the length of the value to replace changes then the substr range needs to change.

[ErikLund_Jensen]

Hi @AshJuri 

A small change to the code provided by @ballardw. This doesn't search for the value of the middle string, but changes any value in position 2, so there is no risk of transforming other occurences of the value.

data want;
  set subject_data;
  subid = catx(' ',scan(subid,1),substr(scan(subid,3),1,4),scan(subid,3));
run;