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
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;
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;
Due to security reason we can't store it into macro variable.
@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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.