Search and Replace in a text column

Accepted Solution Solved
Reply
Regular Contributor
Posts: 223
Accepted Solution

Search and Replace in a text column

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


Accepted Solutions
Solution
‎01-09-2013 05:27 AM
SAS Employee
Posts: 23

Re: Search and Replace in a text column

Posted in reply to NicholasKormanik

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.

View solution in original post


All Replies
SAS Employee
Posts: 23

Re: Search and Replace in a text column

Posted in reply to NicholasKormanik

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,'_');

Regular Contributor
Posts: 223

Re: Search and Replace in a text column

Posted in reply to Fraktalnisse

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'?

Solution
‎01-09-2013 05:27 AM
SAS Employee
Posts: 23

Re: Search and Replace in a text column

Posted in reply to NicholasKormanik

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.

Super Contributor
Posts: 1,636

Re: Search and Replace in a text column

Posted in reply to NicholasKormanik

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

Super Contributor
Posts: 1,636

Re: Search and Replace in a text column

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

Respected Advisor
Posts: 3,156

Re: Search and Replace in a text column

Posted in reply to NicholasKormanik

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

Regular Contributor
Posts: 223

Re: Search and Replace in a text column

Remarkable.  Thanks so much folks.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 309 views
  • 6 likes
  • 4 in conversation