topic Re: Macro for updating column based on occurrence of a substring in SAS Data Management

Macro for updating column based on occurrence of a substring

AshJuri — Mon, 12 Oct 2020 21:13:22 GMT

Hi all, my code currently uses the tranwrd function in order to update a column based on the occurrence of a specified substring. I would like to have a macro that achieves this in order to automate this process since my data set is quite large. Any solutions would be appreciated. My code is below.

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

H7891133 1029 10151024               /*H7891133 1029 14451024*/
H7891133 1029 10241005               /*H7891133 1029 15101005*/
H7891133 1029 10241019               /*H7891133 1029 15101019*/
;
run;

Data update_sub ;
		Set subject_data;
		SUBID_char = put(SUBID, 22.) ;
		/*drop SUBID ;
		rename SUBID_char=SUBID ; */

		if substr(SUBID_char,15,8)='10151024' then do 
		SUBID_char=tranwrd(SUBID_char, "10151024", "14451024");
   		put SUBID_char;
		end;

		if substr(SUBID_char,15,8)='10241005' then do 
		SUBID_char=tranwrd(SUBID_char, "10241005", "15101005");
		put SUBID_char;
		end;

		if substr(SUBID_char,15,8)='10241019' then do 
		SUBID_char=tranwrd(SUBID_char, "10241019", "15101019");
		put SUBID_char;
		end;
		
run;

Re: Macro for updating column based on occurrence of a substring

TomKari — Mon, 12 Oct 2020 15:42:32 GMT

Your code doesn't execute correctly, and I can't figure out what it's supposed to be doing. Get it running correctly, and provide a description of what's its supposed to be doing, and I'm sure it'll be easy to fix!

Tom

Re: Macro for updating column based on occurrence of a substring

Kurt_Bremser — Mon, 12 Oct 2020 15:43:21 GMT

Start by replacing your multiple IFs with a SELECT:

select (substr(SUBID_char,15,8));
  when ('10151024') substr(SUBID_char,15,8) = "14451024";
  when ('10241005') substr(SUBID_char,15,8) = "15101005";
  when ('10241019') substr(SUBID_char,15,8) = "15101019";
  otherwise;
end;

This code can then be automated by reading the "from" and "to" values from a dataset and creating the code by writing it to a temporary file and then including that at the proper location.

filename inc _temp_;

data _null_;
set values;
file inc;
line = 'when ("' !! strip(from) !! '") substr(subid_char,15,8) = "' !! strip(to) !! '";';
put line;
run;

data want;
set have;
select (substr(subid_char,15,8));
  %include inc;
  otherwise;
end;
run;

Re: Macro for updating column based on occurrence of a substring

AshJuri — Mon, 12 Oct 2020 19:05:37 GMT

Hi, thank you very much for your reply. May I ask if you can provide an example of your solution filled with the corresponding inputs in order to call and have it execute? It would help greatly in understanding as I am fairly new to using macros. Thank you once again!

Re: Macro for updating column based on occurrence of a substring

Kurt_Bremser — Mon, 12 Oct 2020 19:32:49 GMT

The dataset values would look like this:

data values;
input (from to) (:$6.);
datalines;
10151024 14451024
10241005 15101005
10241019 15101019
;

Re: Macro for updating column based on occurrence of a substring

AshJuri — Mon, 12 Oct 2020 21:23:02 GMT

Hi, thank you for your reply. I have updated my code and I believe it should execute. The input values are a snippet of values from a much larger data set. I replaced specified substrings in each value using the tranwrd function. For example, in string "H7891133 1029 10151024", the substring "10121024" is replaced by "14451024". Therefore, the output of the string is H7891133 1029 14451024. I would like a macro that achieves this instead of my current approach so that I can just call the macro by inputting the original string and the output that I would like. For example %mnemonic("H7891133 1029 10151024","1445") should replace the string "H7891133 1029 10151024" and have the string "H7891133 1029 14451024" take its place in the data set, or something of that form.

Re: Macro for updating column based on occurrence of a substring

Kurt_Bremser — Tue, 13 Oct 2020 10:19:18 GMT

Let's start by clearly defining the code that is needed to do the actual work.

Is this:

data update_sub;
set subject_data;
SUBID_char = put(SUBID, 22.);
select (substr(SUBID_char,15,8));
  when ('10151024') substr(SUBID_char,15,8) = "14451024";
  when ('10241005') substr(SUBID_char,15,8) = "15101005";
  when ('10241019') substr(SUBID_char,15,8) = "15101019";
  otherwise;
end;
run;

doing what you want?

If yes, which of the elements need to be changed to make the code dynamic?

In particular, will the PUT always work as intended? Think of leading zeroes that would be omitted.

Will the position within the search/resulting string always be the same?

Will the length of the string to be replaced change?

Re: Macro for updating column based on occurrence of a substring

AshJuri — Tue, 13 Oct 2020 15:23:15 GMT

Thank you for your reply. The PUT can be disregarded as the column SUBID is now originally a character variable. Therefore, the updated code would be as shown below. The position within the search/resulting string will always be the same and the length of the string to be replaced will not change. The code does in fact do what I want. However, I was also interested in being able to have a macro that I can call by inputting the original string and the output that I would like, or vice versa. For example the macro call %mnemonic (10151024, 1445); would cause the updating of the string "H7891133 1029 10151024" and have the string "H7891133 1029 14451024" take its place in the data set, or something of that form.

Data update_sub;
set subject_data;
select (substr(SUBID,15,8));
  when ('10151024') substr(SUBID,15,8) = "14451024";
  when ('10241005') substr(SUBID,15,8) = "15101005";
  when ('10241019') substr(SUBID,15,8) = "15101019";
  otherwise;
end;

Re: Macro for updating column based on occurrence of a substring

Kurt_Bremser — Tue, 13 Oct 2020 16:17:15 GMT

As you can see, the only thing that is repeated now (with different values), is the WHEN statement within the SELECT.

A macro would then incorporate this:

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

and would be used like this:

data update_sub;
set subject_data;
select (substr(SUBID,15,8));
  %mnemonic(10151024,14451024)
  %mnemonic(10241005,15101005)
  %mnemonic(10241019,15101019)
  otherwise;
end;
run;

As you see, that does not save much coding, because the SELECT block in itself is already a very efficient tool.

If you have lots of these replacements to do, you are much better off automating the non-macro code with CALL EXECUTE or a dynamically built include.