Need Help to find first instance of word in a SAS string and update it

[nitink26]

Hi all,

I need help on below scenario -

data test;
a1= "dd|bb|aa|aa|cc|aa|dd";
/*I need to update only first instance of aa with " " */
/*need*/
a1= "dd|bb| |aa|cc|aa|dd";

/*First I need to find out the first instance of aa and update it to " " in the same string.*/
/*i tried tranwrd but its removing all the instances of aa with " "  like a1= "dd|bb| | |cc| |dd"; */
/*Also, I will be doing in iterations so every time I need to use updated value of a1*/
run;

[PeterClemmensen]

Try this. This replaces the first instance of |aa| with | | . (In the a2 string)

data test;
   a1 = "dd|bb|aa|aa|cc|aa|dd";
   a2 = prxchange('s/\|aa\|/| |/', 1, a1);
run;

[s_lassen]

One possible problem with your solution: it will not replace the first occurrence in a string that starts with an "aa", like "aa|aa|bb".

I tried putting in look-ahead and look-behind operators in a PRX solution, but the "$|\|" necessary in the look-behind string (beginning of string or "\") is considered variable length and does not work.

[s_lassen · Accepted solution]

You could use PRXCHANGE, which allows you to specify how many times the replacement should be done (2nd parameter) e.g.:

data test;
  a1="dd|bb|aa|aa|cc|aa|dd";
  a1=prxchange('s/aa/ /',1,a1);
run;

If you need to replace only "aa" and not "aa1" or "aaa", it may be better to use FINDW and replace substrings:

data test;
  a1= "dd|bb|aa1|aa1|cc|aa";
  pos=findw(a1,'aa','|');
  if pos then do;
    substr(a1,pos,1)=' ';
    substr(a1,pos+1)=substr(a1,pos+2);
    end;
  drop pos;
run;

Though a bit more tedious, the FINDW solution may also be faster, as the PRX functions generally require more overhead.

Edit note: I put in the "if pos then do;" contdition, as you will otherwise get an error if no "aa" is found.

[Astounding]

This issue of identifying a string (including the possibility of a missing delimiter at the beginning or end) has been around for a while.  Since before parsing functions were added to the software in fact.  Here is an old school approach:

data want;
  set have;
   a1 = "dd|bb|aa|aa|cc|aa|dd";
   found_aa = index("|" || trim(a1) || "|", "|aa|");
   if found_aa > 0 then substr(a1, found_aa, 2) = "  ";
   drop found_aa;
run;

The formula for FOUND_AA is actually trickier than it looks, since we are searching for "|aa|" but want to change the string starting with "aa" not starting with "|".