topic find string in column from another table and replace. in SAS Programming

find string in column from another table and replace.

buju — Mon, 04 Jul 2011 07:21:05 GMT

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.

find string in column from another table and replace.

mojerry2 — Mon, 04 Jul 2011 09:15:46 GMT

let's help.

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

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 into:orig1-:orig99,: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

find string in column from another table and replace.

Ksharp — Mon, 04 Jul 2011 09:19:28 GMT

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

find string in column from another table and replace.

buju — Mon, 04 Jul 2011 10:26:49 GMT

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

find string in column from another table and replace.

buju — Mon, 04 Jul 2011 10:29:44 GMT

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.

find string in column from another table and replace.

Ksharp — Tue, 05 Jul 2011 02:58:20 GMT

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

Ksharp

find string in column from another table and replace.

mojerry2 — Tue, 05 Jul 2011 05:42:55 GMT

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.

find string in column from another table and replace.

Ksharp — Tue, 05 Jul 2011 07:36:51 GMT

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

find string in column from another table and replace.

mojerry2 — Tue, 05 Jul 2011 07:48:20 GMT

true