topic Re: Search and Replace in a text column in SAS Programming

Search and Replace in a text column

NKormanik — Wed, 09 Jan 2013 07:32:34 GMT

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

Re: Search and Replace in a text column

Fraktalnisse — Wed, 09 Jan 2013 09:28:09 GMT

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

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

Re: Search and Replace in a text column

NKormanik — Wed, 09 Jan 2013 10:18:56 GMT

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

Re: Search and Replace in a text column

Fraktalnisse — Wed, 09 Jan 2013 10:27:07 GMT

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.

Re: Search and Replace in a text column

Linlin — Wed, 09 Jan 2013 13:25:30 GMT

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

Re: Search and Replace in a text column

Linlin — Wed, 09 Jan 2013 14:29:36 GMT

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

Re: Search and Replace in a text column

Haikuo — Wed, 09 Jan 2013 15:02:35 GMT

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

Re: Search and Replace in a text column

NKormanik — Wed, 09 Jan 2013 21:55:43 GMT

Remarkable. Thanks so much folks.