BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buju
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
mojerry2
Fluorite | Level 6

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

View solution in original post

8 REPLIES 8
mojerry2
Fluorite | Level 6

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

buju
Calcite | Level 5

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

Ksharp
Super User

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

buju
Calcite | Level 5

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.

Ksharp
Super User

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

Ksharp

mojerry2
Fluorite | Level 6

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.

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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