topic Re: Macro for replacing occurrence of substring in a variable in SAS Programming

Macro for replacing occurrence of substring in a variable

AshJuri — Wed, 07 Oct 2020 18:20:15 GMT

Hello all,

I created a macro for replacing the occurrence of a substring in a variable with a specified number. However, it is returning an error stating "Statement is not valid or it is used out of proper order." Any help would be appreciated.

data testdata;
  infile datalines;
  input oldsub newsub sub;
datalines;                        OUTPUT           
1055	1357	10551024          13571024
1036	1671	10361072          16711072
1089	1752	10891012          17521012
;
run;

%macro mnemonic(update_subjid, new);
	if substr('&update_subjid',1,4)='1055' 
		then do; 
			&new || substr('update_subjid',5,8);
	end;
%mend mnemonic;

%mnemonic (10551024, 1357);

Re: Macro for replacing occurrence of substring in a variable

Kurt_Bremser — Wed, 07 Oct 2020 18:30:27 GMT

Your macro is supposed to create data step code, but you use it outside of a data step.

Before you try to make code dynamic, make sure it works. Please show us your code for a single instance.

Re: Macro for replacing occurrence of substring in a variable

ballardw — Wed, 07 Oct 2020 18:35:40 GMT

Your macro contains data step code that would have to be part of a data step.

Your macro variable

substr('&update_subjid',1,4)

will not be used because macro variables have to be between double quotes.

And you provided one single fixed value to compare, not the variable name, so the values of the variable are not tested.

Your example data has all the values as numeric so you have some issues with the actual values involved as well.

If the object is to actually use the values in the data set then:

data testdata;
  infile datalines;
  input oldsub $ newsub $ sub $;
  if sub =: strip(oldsub) then substr(sub,1,4)=strip(newsub);
datalines;                        
1055	1357	10551024    
1036	1671	10361072    
1089	1752	10891012    
;

Otherwise describe the issue in more detail. If you do not actually have oldsub and newsub in the data then show us what you actually have.

Re: Macro for replacing occurrence of substring in a variable

AshJuri — Wed, 07 Oct 2020 18:55:48 GMT

Thank you for your reply. I have a column called "subjectID" in a data set. I would like to update that column by replacing the occurrence of a sequence of numbers with a specified number. For example, I would like "1357" to replace "1055" in "10551024" in order to become "13571024". I provided the example data in order to show the type of replacement I was trying to achieve. I wanted to use the macro to achieve this.

Updating a column in a data set using a macro

AshJuri — Wed, 07 Oct 2020 19:02:42 GMT

HI,

I have a column called "subjid" in a dataset called "patients". I would like to update that column by replacing the occurrence of a sequence of numbers with a specified number using a macro. For example, I would like "1357" to replace "1055" in "10551024" in order to become "13571024". Below is my code. However, I am receiving an error stating "Statement is not valid or it is used out of proper order." How can I fix this? Thank you in advance.

%macro mnemonic(update_subjid, new);
	if substr('&update_subjid',1,4)='1055' 
		then do; 
			&new || substr('update_subjid',5,8);
	end;
%mend mnemonic;

%mnemonic (10551024, 1357)

Re: Macro for replacing occurrence of substring in a variable

ballardw — Wed, 07 Oct 2020 20:15:54 GMT

@AshJuri wrote:

Thank you for your reply. I have a column called "subjectID" in a data set. I would like to update that column by replacing the occurrence of a sequence of numbers with a specified number. For example, I would like "1357" to replace "1055" in "10551024" in order to become "13571024". I provided the example data in order to show the type of replacement I was trying to achieve. I wanted to use the macro to achieve this.

So how many of these do you have to replace?

Another approach. I hope it is obvious where your old and new values are going.

data testdata;
   infile datalines;
   input  sub $;
   select (substr(sub,1,4));
      when ('1055') substr(sub,1,4)='1357';     
      when ('1036') substr(sub,1,4)='1671';     
      when ('1089') substr(sub,1,4)='1752';     
      otherwise;
   end;
datalines;                        
10551024    
10361072    
10891012    
11111111
;

I include a value of your Sub variable that doesn't have any replacement to show nothing happens.

The Select/when/end is like having a bunch of If/then/else statements. The WHEN contains one or a comma separated list of values. The values should match something from the value after the SELECT, in this case the first four characters of the string.

The Otherwise statement lets you do something for values that do not match any of the when lists.

Re: Updating a column in a data set using a macro

PaigeMiller — Thu, 08 Oct 2020 10:35:17 GMT

@AshJuri

I believe the earlier request was this:

Please show us working DATA step code, with no macros and no macro variables, that does the task you want on a single input ID. That's where the solution starts. If you can't get working DATA step code, with no macros and no macro variables, on a single input ID, then your macro will never work either.