topic Re: find and replace each character in a string with lookup dataset value in SAS Programming

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

Banu2318 — Wed, 03 Aug 2022 03:59:47 GMT

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

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

andreas_lds — Wed, 03 Aug 2022 05:54:55 GMT

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;

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

PeterClemmensen — Wed, 03 Aug 2022 06:49:13 GMT

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;

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

Ksharp — Wed, 03 Aug 2022 12:46:57 GMT

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;

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

Banu2318 — Thu, 04 Aug 2022 00:09:50 GMT

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

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

Banu2318 — Thu, 04 Aug 2022 00:48:17 GMT

its taking longer time to apply format for multiple columns.

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

Tom — Thu, 04 Aug 2022 02:05:23 GMT

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