In my previous post, we addressed the problem of inserting substrings into SAS character strings. In this post we will solve a reverse problem of deleting substrings from SAS strings.
These two complementary tasks are commonly used for character data manipulation during data cleansing and preparation to transform data to a shape suitable for analysis, text mining, reporting, modeling and decision making.
As in the previous case of substring insertion, we will cover substring deletion for both, character variables and macro variables as both data objects are strings.
The following diagram illustrates what we are going to achieve by deleting a substring from a string:
Have you noticed a logical paradox? We take away a “pieceof” cake and get the whole thing as result!
Now, let’s get serious.
Deleting all instances of a substring from a character variable
Let’s suppose we have a variable STR whose values are sprinkled with some undesirable substring ‘<br>’ which we inherited from some HTML code where tag <br> denotes a line break. For our purposes, we want to remove all instances of those pesky <br>’s. First, let’s create a source data set imitating the described “contaminated” data:
data HAVE; infile datalines truncover; input STR $100.; datalines; Some strings<br> have unwanted sub<br>strings in them<br> <br>A s<br>entence must not be cont<br>aminated with unwanted subs<br>trings Several line<br> breaks<br> are inserted here<br><br><br> <br>Resulting st<br>ring must be n<br>eat and f<br>ree from un<br>desirable substrings Ugly unwanted substrings<br><br> must <br>be<br> removed <br>Let's remove them <br>using S<br>A<br>S language Ex<br>periment is a<br>bout to b<br>egin <br>Simpli<br>city may sur<br>prise you<br><br> ;
This DATA step creates WORK.HAVE data set that looks pretty ugly and is hardly usable:
The following code, however, cleans it up removing all those unwanted substrings ‘<br>’:
data WANT (keep=NEW_STR); length NEW_STR $100; SUB = '<br>'; set HAVE; NEW_STR = transtrn(STR,'<br>',trimn('')); run;
After this code runs, the data set WANT will look totally clean and usable:
- We use .
The TRANSTRN function is similar to TRANWRD function which replaces all occurrences of a substring in a character string. While TRANWRD uses a single blank when the replacement string has a length of zero, TRANSTRN does allow the replacement string to have a length of zero which essentially means removing.
- TRIM() function which removes trailing blanks from a character string and returns one blank if the string is missing. However, when it comes to removing (which is essentially replacement with zero length substring) the ability of TRIMN function to return a zero-length string makes all the difference.
Deleting all instances of a substring from a SAS macro variable
For macro variables, I can see two distinct methods of removing all occurrences of undesirable substring.
Method 1: Using SAS data step
Here is a code example:
%let STR = Some strings<br> have unwanted sub<br>strings in them<br>; %let SUB = <br>; data _null_; NEW_STR = transtrn("&STR","&SUB",trimn('')); call symputx('NEW',NEW_STR); run; %put &=STR; %put &=NEW;
In this code, we stick our macro variable value &STR in double quotes in the transtrn() function as the first argument (source). The macro variable value &SUB, also double quoted, is placed as a second argument. After variable NEW_STR is produced free from the &SUB substrings, we create a macro variable NEW using
%let STR = Some strings<br> have unwanted sub<br>strings in them<br>; %let SUB = <br>; %let NEW = %sysfunc(transtrn(&STR,&SUB,%sysfunc(trimn(%str())))); %put &=STR; %put &=NEW;
Deleting selected instance of a substring from a character variable
In many cases we need to remove not all substring instances form a string, but rather a specific occurrence of a substring. For example, in the following sentence (which is a quote by Albert Einstein) “I believe in intuitions and inspirations. I sometimes feel that I am right. I sometimes do not know that I am.” the second word “sometimes” was added by mistake. It needs to be removed. Here is a code example presenting two solutions of how such a deletion can be done:
data A; length STR STR1 STR2 $250; STR = 'I believe in intuitions and inspirations. I sometimes feel that I am right. I sometimes do not know that I am.'; SUB = 'sometimes'; STR_LEN = length(STR); SUB_LEN = length(SUB); POS = find(STR,SUB,-STR_LEN); STR1 = catx(' ', substr(STR,1,POS-1), substr(STR,POS+SUB_LEN)); /* solution 1 */ STR2 = kupdate(STR,POS,SUB_LEN+1); /* solution 2 */ put STR1= / STR2=; run;
The code will produce two correct identical values of this quote in the SAS log (notice, that the second instance of word “sometimes” is gone):
STR1=I believe in intuitions and inspirations. I sometimes feel that I am right. I do not know that I am.
STR2=I believe in intuitions and inspirations. I sometimes feel that I am right. I do not know that I am.
- FIND() function determines position POS of the substring SUB to be deleted in the string STR. In this particular example, we used the fact, that the second occurrence of word “sometimes” is the first occurrence of this word when counted from right to left. That is indicated by the negative 3-rd argument (-STR_LEN) which means that FIND function searches STR for SUB starting from position STR_LEN from right to left.