find and replace each character in a string with lookup dataset value

[Banu2318]

I have dataset Account_details which is having account_id other dataset "lookup" like below

unmasked masked

a m

b n

need to change account_id each character with this value like below

old Account_ID   new Account_ID

Abwok     mnwok

[andreas_lds]

Sounds like an annoying homework.

Please note that "A" is not "a", so do you want to compare case-insensitive?

Here's an idea, i assumed that "lookup" has only single chars for in both variables.

EDIT:

data have;
   length account_id $ 10;
   input account_id;
   datalines;
Abwok
;

data lookup;
   length unmasked masked $ 1;
   input unmasked masked;
   datalines;
a m
b n
;

data lookup_format;
   set lookup(rename=(unmasked = start masked = label));
   retain FmtName '$Lookup' Type 'J';
run;

proc format cntlin=lookup_format;
run;

data want;
   set have;
   length new_account_id $ 10;
   
   new_account_id = account_id;
   
   do i = 1 to lengthn(account_id);
      substr(new_account_id, i, 1) = input(lowcase(char(new_account_id, i)), $Lookup.);
   end;
   
   drop i;
run;

[Banu2318]

its taking longer time to apply format for multiple columns.

[PeterClemmensen]

Here is an alternative. Also, I agree with @andreas_lds, seems like annoying homework 😉

proc sql noprint;
   select unmasked
        , masked
   into :u separated by ''
      , :m separated by ''
   from lookup
   ;
quit;

data want;
   set have;
   new_account_id = translate(lowcase(account_id), "&m.", "&u.");
run;

[Banu2318]

Due to security reason we can't store it into macro variable.

[Tom]

---

@Banu2318 wrote:

Due to security reason we can't store it into macro variable.

---

There is no reason you couldn't use a macro variable.  I suspect the issue you have is you don't want the code/decode pairs printed to the SAS log, but you can easily prevent that.

But there shouldn't be any need to store the code/decode strings into macro variables.  It will be easier to keep them in dataset variables anyway.  Your current structure is inefficient of use, so first transform it so you have FROM and TO variables you use with the TRANSLATE function.

data lookup_fixed;
  length from to $256 ;
  do until (eof);
    set lookup;
    substr(from,_n_,1)=unmasked;
    substr(to,_n_,1)=masked;
  end;
  drop unmasked masked;
run;

Now to transform your ACCOUNT_DETAILS dataset you can use something like this:

data account_details_masked ;
  if _n_=1 then set lookup_fixed;
  set account_details;
  account_id = translate(account_id,to,from);
  drop from to;
run;

You could easily extend that to multiple variables by using an ARRAY.

For example if in addition to ACCOUNT_ID you also wanted to mask NAME and ADDRESS.

data account_details_masked ;
  if _n_=1 then set lookup_fixed;
  set account_details;
  array mask account_id name address ;
  do index=1 to dim(mask);
    mask[index] = translate(mask[index],to,from);
  end;
  drop from to index;
run;

[Ksharp]

data have;
   length account_id $ 10;
   input account_id;
   datalines;
Abwok
;

data lookup;
   length unmasked masked $ 1;
   input unmasked masked;
   datalines;
a m
b n
;

proc sql noprint;
select unmasked into :unmasked separated by '' from lookup;
select upcase(unmasked) into :unmasked2 separated by '' from lookup;

select masked into :masked separated by '' from lookup;
quit;

data want;
 set have;
 want=translate(account_id,"&masked.","&unmasked.");
 want=translate(want,"&masked.","&unmasked2.");
run;