Find and replace

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Find and replace

Hello,

    I am stuck on a find and replace problem (SAS 9.2).  I need to find out if a variable contains the value of another variable and if it does, replace the value with another variables value. 

 

for example:

HAVE

varID var1 var2 var3

1 "Store B100 - ID B101" B100 "Sears"

1 "Store B100 - ID B101" B101 1234

2 "Street B102" B102 "999 Main St"

 

WANT

varID var1 var2 var3 var4

1 "Store B100 - ID B101" B100 "Sears" "Store Sears - ID B100"

1 "Store B100 - ID B101" B101 1234 "Store B101 - ID 1234"

2 "Street B102" B102 "999 Main St" "Street 999 Main St"

 

Ideally, var4 has all the B's replaced based on varID (i.e. Store Sears - ID 1234), but I would appreciate even just replacing the information from a single row.

 

1)  How can I tell if var1 contains the value of var2?  Taking the first case as an example:  How can I tell if "Store B100 - ID B101" contains B100?

2)  How can I replace the B values in var1 with the corresponding values in var3?

 

So if var1 contains var2 then replace any matching var2 values in var1 with value of var3.

 

I have tried using INDEX and TRANWRD, but I can only make it work when I type in the B value manually.

I.E. the following works to some degree

if INDEX(var1,"B101") then do;

var4 = TRANWRD(var1,"B101",var3);

 

but the following does not:

if INDEX(var1,var2) then do;

var 4 = TRANWRD(var1,var2,var3);

 

Any suggestions?

 

Thanks in advance.

 

 

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎03-30-2016 08:28 AM
Trusted Advisor
Posts: 1,114

Re: Find and replace

Hello @sas-inquirer,

 

How about this?

data have;
input varID var1 & :$30. var2 :$10. var3 & :$30. ;
cards;
1  "Store B100 - ID B101"  B100  "Sears"
1  "Store B100 - ID B101"  B101  1234
2  "Street B102"  B102  "999 Main St"
;

data want;
do until(last.varID);
  set have;
  by varID;
  length var4 $30;
  if index(var1,trim(var2)) then var4 = tranwrd(coalescec(var4,var1),trim(var2),trim(dequote(var3)));
end;
run;

View solution in original post


All Replies
Regular Contributor
Posts: 211

Re: Find and replace

Data HAVE;
length varID 3 var1 $30 var2 $4 var3 $20;
infile datalines delimiter=',';
input varID var1 $ var2 $ var3 $ ;
datalines;
1,Store B100 - ID B101,B100,Sears
1,Store B100 - ID B101,B101,1234
2,Street B102,B102,999 Main St
;
run;

/* Want */
/*
varID var1 var2 var3 var4
1 "Store B100 - ID B101" B100 "Sears" "Store Sears - ID B100"
1 "Store B100 - ID B101" B101 1234 "Store B101 - ID 1234"
2 "Street B102" B102 "999 Main St" "Street 999 Main St"
*/

data want;
    set have;
    if INDEX(var1,STRIP(vvalue(var2))) then do;
    var4 = TRANWRD(var1,STRIP(vvalue(var2)),STRIP(vvalue(var3)));
    end;
run;

Grand Advisor
Posts: 10,239

Re: Find and replace

Try if INDEX(var1,strip(var2)) then do ...

You may have leading or trailing spaces. Other issues to look at: "B101" will not match "B 101" (space embedded) or "b101" (case).

 

Tranwrd may have similar issues with var2 and possibly var3 introducing additional spaces so you might try using strip with both of those.

Since we don't actually have your data set and possibly would not have the same potential leading or trailing spaces in your data we can't quite test everything.

 

If you are getting any warnings in the log that might help.

Also posting the results you actually get with the code would help diagnose specific issues.

 

You have issues with your first two wants as you are trying to replace "B100 - ID B101" but only searching for B100 or B101. Tranwrd would not get what you want as it would leave part of that string in place. Replacing B100 with Sears would yeild "Sears - ID B101"

 

So if there is more than Var2 to search for you'll need to add additional logic.

 

 

Solution
‎03-30-2016 08:28 AM
Trusted Advisor
Posts: 1,114

Re: Find and replace

Hello @sas-inquirer,

 

How about this?

data have;
input varID var1 & :$30. var2 :$10. var3 & :$30. ;
cards;
1  "Store B100 - ID B101"  B100  "Sears"
1  "Store B100 - ID B101"  B101  1234
2  "Street B102"  B102  "999 Main St"
;

data want;
do until(last.varID);
  set have;
  by varID;
  length var4 $30;
  if index(var1,trim(var2)) then var4 = tranwrd(coalescec(var4,var1),trim(var2),trim(dequote(var3)));
end;
run;
Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: Find and replace

Can you post test data in the form of a datastep so we don't have to type it all in.  Now to clarify your logic, can you not just split the string a bit:

 

data want;
  set have;
  var4=tranwrd(scan(var1,1,"-"),var2,var3)||scan(var1,2,"-");
run;

 

This takes the first part of the string, before the "-", replaces the text from var2 with the text from var3, then concatenates back the second part of var1.

Occasional Contributor
Posts: 19

Re: Find and replace

Thank you all so much! I love the simplicity of your solutions. They all work and are all great optons. Freelance Reinhard your solution works beautifully for my real data. I can't tell you how much I appreciate everyone's time and quick responses!
Occasional Contributor
Posts: 19

Re: Find and replace

RW9 you are absolutely right. I should have put the information in a data step so it didn't have to be typed in. I will do that in any future posts.
☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 489 views
  • 9 likes
  • 5 in conversation