Search and Replace in a text column

[NKormanik]

Here is a pretty basic question for which the answer doesn't seem to exist on this site:

Column name is "Combo."  Type of column is text.

First few observations in the column:

20101_21603

20102_21603

20103_21603

20104_21603

20105_21603

I'd like to do a Search and Replace for "21603."  I would like to replace that with nothing (in other words, delete it).

(In a second step I'll do the same with "_"  Objective being to get the first part, i.e., "20101," alone by itself.)

Any help greatly appreciated.

Nicholas Kormanik

[Fraktalnisse]

Hi!

If you would like to take out the first 5 characters, you can use combo=substr(combo,1,5);

If you would like to take out everything before _, you can use combo=scan(combo,1,'_');

or

if you would like to search for 21603 and replace that with nothing, use combo=transtrn(combo,'21603',trimn(''));

if you would like to remove all _, use combo=compress(combo,'_');

[NKormanik]

This will be done on an existing data file.  Is what you've written above part of a larger set of statements?  Or is it a stand-alone run-type line?

Why treat the '_' differently than the '21603'?

[Fraktalnisse · Accepted solution]

Hi!

You have to write a complete data step. For example:

data NewData;

  set InputData;

  combo=transtrn(combo,'21603',trimn(''));

run;

If you would like to remove certain characters from a text string, the compress function is easier than transtrn. But compress doesn't work for longer substrings, if you would like to remove 21603 you can't use compress.

[Linlin]

data have;

input combo $11.;

cards;

20101_21603

20102_21603

20103_21603

20104_21603

20105_21603

;

data want;

length newvar $5;

set have;

newvar=scan(combo,1,'_');

proc print;run;

                                  Obs    newvar       combo

                                    1     20101     20101_21603
                                    2     20102     20102_21603
                                    3     20103     20103_21603
                                    4     20104     20104_21603
                                    5     20105     20105_21603

[Linlin]

or

data have;
input combo $11.;
cards;
20101_21603
20102_21603
20103_21603
20104_21603
20105_21603
20101_88888
20102_88888
20103_88888
;
data want;
length newvar $11;
set have;
newvar=ifc(find(combo,'21603'),scan(combo,1,'_'),combo);
proc print;run;
                                 Obs    newvar            combo

                                 1     20101                20101_21603
                                 2     20102                20102_21603
                                 3     20103                20103_21603
                                 4     20104                20104_21603
                                 5     20105                20105_21603
                                 6     20101_88888    20101_88888
                                 7     20102_88888    20102_88888
                                 8     20103_88888    20103_88888

[Haikuo]

If you don't mind some PRX functions:

data have;

input combo $11.;

cards;

20101_21603

20102_21603

20103_21603

20104_21603

20105_21603

;

data want;

set have;

  combo=prxchange('s/_21603//',-1,combo);

run;

proc print;run;

Haikuo

[NKormanik]

Remarkable.  Thanks so much folks.