Replacing characters in field

[dblan]

I've got a situation where I need to try to replace specific combinations of characters in a field with another character. Here is as an example of what the starting field would have in it:

data data_tbl;
ProcessFlow = 'AbBcCa';
run;

I've been given a conversion table with that coverts a 2digit UPPERCASE/lowercase combination to "F" or "B" (representing Forward or Backward). Example:

data convert_tbl; 
input Key $ NewKey $;
datalines;						
Ab	F
Bc	F
Ca	B
;
run;

The end result after the convertion would be: "FFB".

The data table has about 50,000 records and there are about 120 rows in the conversion table.

I'm confused on how to join the data table to the conversion table. Anyone have any ideas on how this might be done?

 

[ChrisW75]

oooh, this looks like a fun one.
OK, first off, you can't "join" these two files.
Secondly the way to change characters in a string to other characted is the translate function, strings of characters uses the tranwrd function.

http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000215027.htm

There's 2 ways go do this, one is to hard code the translations, a simple way to do this is to copy and paste the lookup table into Excel, and use Excel to build up the lines of code needed to translate the characters from one to the other.
The seocnd way to do it (preferable if the lookup is likely to change in the future) is to use some macro code to build up the translations for you.

Effectively, either way, you want to end up with code that looks like this:-

   process_flow = tranwrd(process_flow,'Ab','F');
   process_flow = tranwrd(process_flow,'Bc','F');
   process_flow = tranwrd(process_flow,'Ca','B');

One factor to consider here is this - will making 1 translation, end up giving you a resultant combination that'll get picked up by a subsequent translation (i.e. could one of the translations later on be changing 'FF' to something else?). If htis is the case, some careful thought needs to be applied to how to structure this. You might be able to get around it by sorting the list in a specific order to avoid this.

The easiest method to build this code dynamically is to write a datastep which writes out a piece of code which you can then %include into the program.
Something like this:-

data _null_;
  set convert_tbl end=eof;
  file dummy recfm=v lrecl=2056;

  if _n_ = 1 then
  do;
    put 'data test2;';
    put '  set data_tbl;';
    put ' ';
    put '  new_processflow = processflow;';
  end;

  put '  new_processflow = tranwrd(new_processflow,' key ',' newkey ');';

  if eof then
  do;
    put ' ';
    put 'run;';
run;

%include dummy;

Before running the dummy program, check the code that it contains to make sure the code you've produced does what you want it to.







Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.