How can I find and replace specific text in a SAS data set?
<p>I have a data set with 400 observations of 4 digit codes which I would like to pad with a space on both sides</p>

<pre><code>ex. Dataset 
obs code
1   1111 
2   1112
3   3333
.
.
.
400 5999
</code></pre>

<p>How can I go through another large data set and replace every occurrence of any of the padded 400 codes with a " ".</p>

<pre><code>ex. Large Dataset
obs text 
1   abcdef 1111 abcdef
2   abcdef 1111 abcdef 1112 8888
3   abcdef 1111 abcdef 11128888
... 
</code></pre>

<p>Data set that I want</p>

<pre><code>ex. New Data set
obs text
1   abcdef   abcdef
2   abcdef   abcdef   8888
3   abcdef   abcdef 11128888
...
</code></pre>

<p>Note: I'm only looking to replace 4 digit codes that are padded on both sides by a space. So in obs 3, 1112 won't be replaced.</p>

<p>I've tried doing the following proc sql statement, but it only finds and replaces the first match, instead of all the matches.</p>

<pre><code>proc sql;  
    select   
    *,  
    tranwrd(large_dataset.text, trim(small_dataset.code), ' ') as new_text  
from large_dataset  
    left join small_dataset  
    on findw(large_dataset.text, trim(small_dataset.code))
;
quit;
</code></pre>