topic Complex Find and Replace using SAS? in SAS Enterprise Guide

Complex Find and Replace using SAS?

IgawaKei29 — Wed, 19 Dec 2018 15:07:31 GMT

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.

Re: Complex Find and Replace using SAS?

ballardw — Wed, 19 Dec 2018 15:25:03 GMT

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.

Re: Complex Find and Replace using SAS?

FredrikE — Wed, 19 Dec 2018 15:26:18 GMT

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

Re: Complex Find and Replace using SAS?

IgawaKei29 — Wed, 19 Dec 2018 15:41:47 GMT

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

Re: Complex Find and Replace using SAS?

IgawaKei29 — Wed, 19 Dec 2018 15:42:24 GMT

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

Re: Complex Find and Replace using SAS?

FreelanceReinh — Wed, 19 Dec 2018 15:43:09 GMT

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