Importing Excel data and using the data for Masking data in dataset

[Patrick]

Here you go:

data _null_;
/*  file print;*/
  file codegen;
  set colsToMask;
  by libname memname;
  if first.memname then
    do;
      put 
        "data &outlib.." memname ';' /
        '  set ' libname +(-1) '.' memname ';'
        ;
    end;
  if not missing(name) then
    put @3 'if not missing(' name ') then do;' ;
  if type='num' then 
    put @5 name '=9999;'; 
  else 
  if type='char' then
    do;
      if upcase(name)='SURNAME' then 
        do;
          put @5 name '= cats("TEST", _n_) ;'; 
        end;
      else
        do;
          _replace=repeat('Z',min(len-1,10));
          put 
            @5 name '="' _replace +(-1)'" ;' 
            ; 
        end;
    end;
  if not missing(name) then
    put @3 'end;' ;

  if last.memname then
    put 'run;' ;
run;

[Kadz_sas1990]

Thanks Patrick working good 🙂

[Kadz_sas1990]

@Patrick
The code throws an error in fields having length as 1, for example Gender is one field to be masked with lengh 1 and type character it has values like M, F or U. but the code not masking it leaving blank values in it. and produces a Warning as below:
WARNING: In a call to the CATS function, the buffer allocated for the result was not long enough to contain the concatenation of
all the arguments. The correct result would contain 5 characters, but the actual result might either be truncated to 1
character(s) or be completely blank, depending on the calling environment. The following note indicates the left-most
argument that caused truncation.
NOTE: Argument 1 to function CATS('TEST',1) at line 163 column 18 is invalid.

[Kadz_sas1990]

it worked in the previous code
if type='num' then
put ' ' name '=9999;';
else
if type='char' then
do;
put @3 name '= cats("TEST", _n_) ;';
end;
if last.memname then
put 'run;' ;
run;


GOT output like 'T' in masked column instead of 'TEST_n_' if the column length is 1

[Patrick]

---

@Kadz_sas1990 wrote:
it worked in the previous code
if type='num' then
put ' ' name '=9999;';
else
if type='char' then
do;
put @3 name '= cats("TEST", _n_) ;';
end;
if last.memname then
put 'run;' ;
run;


GOT output like 'T' in masked column instead of 'TEST_n_' if the column length is 1

---

If your source column has a length of 1 then you can't store more than one character in it. That's why you get the truncation warning and only the first character T as a result.

Attached the last cut of all the code versions I've posted for you. That works totally fine also for character variables with a length of 1. It will store Z in it.

The code bit with cats("TEST", _n_)  was for the special treatment of variables where you've asked for a different string with a counter. This will of course only work for character variables with a sufficient string length.

Please try to understand the code I've provided to you before you change it. This code was always meant as a template for you and it was also meant as a quick solution for some adhoc-level data masking exactly the way you've asked for. If you/your company need professional level ongoing data masking then you need to look into the SAS Federation Server or data masking options provided by databases.

I'm now done with this thread and won't answer further questions.

[Kadz_sas1990]

SAS Federation Server licensed , I am not aware of this