Complex Find and Replace using SAS?

[IgawaKei29]

So I came across an issue with some data and I need to figure out a way for SAS to scan the dataset and replace with certain criteria.

The dataset is roughly 1mil rows so that adds to my complexity with trying to work this out.

So what I am looking for is that everytime the row meets the following criteria:

Name in ('Matt') Segment in ('Tall') Number in ('20') Color in ('Blue')

I would need it to replace with:

Name in ('John') Segment in ('Short') Number in ('40') Color in ('Green')

I am just not sure how to do this replacing data with very specific row criteria like this.  I was thinking about a If/then statement in proc sql, but again I am not 100% sure of the syntax needed.

Thank you for anyone who can help.

[ballardw · Accepted solution]

I think a data step would be easier than the equivalent SQL if I understand the entire issue.

data want;
   set have;
   if Name in ('Matt')  and Segment in ('Tall') and Number in ('20') and Color in ('Blue') then do;
      Name    ='John';
      Segment ='Short';
      Number  ='40';
      Color   ='Green';   
   end;
run;

Since SQL does not support IF/Then quite you would be using a CASE statement and would need a separate case statement for each variable to change.

Of course one question is your "number" variable actually character valued as implied by your quotes around the variable? If number is actually numeric you would not use quotes at all for that variable.

[IgawaKei29]

This was great, thank you. I have filed this away into the code book!

[FredrikE]

Hi!

I am not sure about the performance, but you can try to do it with regular expressions:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002601591.htm

🙂

//Fredrik

[IgawaKei29]

Yeah performance might be an issue, but I am going to try this out as well. Never hurts to try a few different ways

[FreelanceReinh]

---

@IgawaKei29 wrote:

The dataset is roughly 1mil rows so that adds to my complexity with trying to work this out. 

---

Don't use the original dataset for testing. Just create a temporary dataset consisting of a few representative observations from it and develop the code using this small dataset.

Just to add to @ballardw's suggestion: If your replacement strings are longer than the original ones (as is the case in two of your examples), you need to make sure that the lengths of the respective character variables are sufficient to accommodate the longer values. If they are not, redefine the lengths as shown in the example below to avoid truncation:

data have;
color='Blue';
run;

data want;
length color $5; /* important: LENGTH statement before SET statement */
set have;
if color='Blue' then color='Green';
run;