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.
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
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
thank you very much : it's work very well. it's help me.
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
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.
then use indexw() or index() find() to search that word.
Ksharp
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.
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
true
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.