DATA Step, Macro, Functions and more

find string in column from another table and replace.

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

find string in column from another table and replace.

hello, (I started in sas)

I have two tables :

data first;

infile cards dlm=',';

input city$ :30. any$ :20. ;

CARDS;

oakland is very beautifull,azerty

I never go to oakland,qwerty

abc fremont fgh ,anything

;

run;

and a secondary :

data transco;

infile cards dlm=',';

input field1$ :30. field2$ :20. ;

CARDS;

oakland,berkeley

fremont,newark

stanford,loyola

;

run;

And I would like find any data from field1 in my secondary table in first table for city, and if string match, I would like to replace him from field2.

ex : oakland is very beautifull >> transform in : berkeley is very beautifull

      I never go to oakland >> I never go to berkeley

      abc fremont fgh >> abc loyola fgh

thank you for your help.


Accepted Solutions
Solution
‎07-04-2011 05:15 AM
Contributor
Posts: 28

find string in column from another table and replace.

let's help.

your example contains an error : abc fremont fgh >> abc newark fgh. Smiley Wink

here's the code.

data first;
infile cards dlm=',';
input city$ :30. any$ :20. ;
CARDS;
oakland is very beautifull,azerty
I never go to oakland,qwerty
abc fremont fgh ,anything
testing empty match,mojojo
;
run;

data transco;
infile cards dlm=',';
input field1$ :30. field2$ :20. ;
CARDS;
oakland,berkeley
fremont,newark
stanford,loyola
;
run;

/* let's get the input char (orig) and the changer char (dest) */
proc sql;
select field1,field2 intoSmiley Surprisedrig1-Smiley Surprisedrig99,:dest1-:dest99 from transco;
quit;

%put number of observations in transco : &sqlobs;

/* now search the string in the dataset and tell if it's found */
data first2 (drop=i);
set first;
format orig $30. dest $20. i 10. found 1.;
found=0;
do i=1 to &sqlobs;
orig=symget(compress("orig"!!i));
dest=symget(compress("dest"!!i));/*
test=compress("orig"!!i);*/
if index(city,trim(orig)) then do; found=1; output;end;
end;
if found=0 then do;orig='';dest='';output;end;
run;

/* now replace the string */
data first3;
set first2;
if found then do;
city2=tranwrd(city,trim(orig),trim(dest));
end;
else do;
city2=city;
end;
run;

I do the search and replace in two different datasteps but you can do it in one step.

Take care if you do this to gat out the do loop or you could replace twice a city.

enjoy

View solution in original post


All Replies
Solution
‎07-04-2011 05:15 AM
Contributor
Posts: 28

find string in column from another table and replace.

let's help.

your example contains an error : abc fremont fgh >> abc newark fgh. Smiley Wink

here's the code.

data first;
infile cards dlm=',';
input city$ :30. any$ :20. ;
CARDS;
oakland is very beautifull,azerty
I never go to oakland,qwerty
abc fremont fgh ,anything
testing empty match,mojojo
;
run;

data transco;
infile cards dlm=',';
input field1$ :30. field2$ :20. ;
CARDS;
oakland,berkeley
fremont,newark
stanford,loyola
;
run;

/* let's get the input char (orig) and the changer char (dest) */
proc sql;
select field1,field2 intoSmiley Surprisedrig1-Smiley Surprisedrig99,:dest1-:dest99 from transco;
quit;

%put number of observations in transco : &sqlobs;

/* now search the string in the dataset and tell if it's found */
data first2 (drop=i);
set first;
format orig $30. dest $20. i 10. found 1.;
found=0;
do i=1 to &sqlobs;
orig=symget(compress("orig"!!i));
dest=symget(compress("dest"!!i));/*
test=compress("orig"!!i);*/
if index(city,trim(orig)) then do; found=1; output;end;
end;
if found=0 then do;orig='';dest='';output;end;
run;

/* now replace the string */
data first3;
set first2;
if found then do;
city2=tranwrd(city,trim(orig),trim(dest));
end;
else do;
city2=city;
end;
run;

I do the search and replace in two different datasteps but you can do it in one step.

Take care if you do this to gat out the do loop or you could replace twice a city.

enjoy

New Contributor
Posts: 3

find string in column from another table and replace.

thank you very much : it's work very well. it's help me.

Super User
Posts: 9,681

find string in column from another table and replace.

abc fremont fgh >> abc loyola fgh should be abc fremont fgh >> abc newark fgh ?

I have to leave now.will be here tomorrow.

data first;
infile cards dlm=',';
input city$ :30. any$ :20. ;
CARDS;
oakland is very beautifull,azerty
I never go to oakland,qwerty
abc fremont fgh ,anything
;
run;

data transco;
infile cards dlm=',';
input field1$ :30. field2$ :20. ;
CARDS;
oakland,berkeley
fremont,newark
stanford,loyola
;
run;
data want;
 set first;
 do i=1 to _nobs;
  set transco nobs=_nobs point=i;
  if findw(city,strip(field1)) then city=tranwrd(city,strip(field1),strip(field2));
 end;
drop field:;
run;

Ksharp

New Contributor
Posts: 3

find string in column from another table and replace.

thank you its work : after I'm replace function findw by findc(because I use sas 9.1 not 9.2), but it's godd example, and it's very easy to use. thank a lot.

Super User
Posts: 9,681

find string in column from another table and replace.

then use indexw() or index() find() to search that word.

Ksharp

Contributor
Posts: 28

find string in column from another table and replace.

watch out the example of ksharp doesn't quit when the city was found.

so if you have in your example to change oakland to berkeley but a few records after you have to change berkeley to new ark in your transco table then it could give some problems.

Super User
Posts: 9,681

find string in column from another table and replace.

Hi. I understand what you mean ,It is about tree problem and as you think there is also be a dead loop problem, such as

berkeley -> oakland

oakland ->  berkeley.

If as your logic that could be :

berkeley -> oakland

oakland -> washington

Why not directly write as :

berkeley -> washington

So it is hard to judge something without seeing op's orgin data .

Ksharp

Contributor
Posts: 28

find string in column from another table and replace.

true Smiley Wink

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 370 views
  • 3 likes
  • 3 in conversation