topic Re: Mask Data in SAS Programming

Mask Data

Ronein — Sun, 16 Jul 2023 13:12:59 GMT

Hello
I have a dataset which contains a field for identifying a customer ( cust_id).
I need to mask the cust_id (before I send it to other company).
Then the Other company will send me back the data set (With extra columns) and I need to create a new column with the original customer ID.
May anyone show code how to mask the ID column and then how to find the original customer ID value?
Some notes-
1- customer id is unique number for each row that cannot repeat.
2- The new masked customer id should also be unique value for each row
3- The target is to create a new column called masked_cust_id and then have the ability to calculate original cust_id from masked_cust_id

Data have;
input cust_ID;
cards;
33948398
54897733
98376333
;
run;

Re: Mask Data

Tom — Sun, 16 Jul 2023 15:17:57 GMT

Just make a new dataset that has the two id's.

The masked id can probably just be generated sequentially.

data masked_cust_id ;
  set have;
  by cust_id;
  if first.cust_id;
  masked_cust_id + 1 ;
  keep cust_id masked_cust_id;
run;

Then when you want to send out the actual data merge the two and drop the real id.

data for_export ;
  merge masked_cust_id real_data(in=in2);
  by cust_id;
  if in2;
  drop cust_id;
run;

And when you get back data merge again and add back the real id.

data want ;
  merge masked_cust_id for_import(in=in2);
  by masked_cust_id;
  if in2;
run;

Re: Mask Data

Ronein — Sun, 16 Jul 2023 18:16:29 GMT

Thank you for the reply. However I forgot to mention that let's assume that it is forbidden by the rules to create such backup data set that convert fake customer id to real customer id. I would like to find other solution that convert real customer id to fake customer id and then can convert it back from fake customer id to real customer id,thanks

Re: Mask Data

Tom — Sun, 16 Jul 2023 18:57:35 GMT

@Ronein wrote:
Thank you for the reply. However I forgot to mention that let's assume that it is forbidden by the rules to create such backup data set that convert fake customer id to real customer id. I would like to find other solution that convert real customer id to fake customer id and then can convert it back from fake customer id to real customer id,thanks

Then you are probably out of luck. You would need to create some type of algorithm for encrypting the values that you need to be able to reverse. That means someone could break it and convert the values back to the original ids.

It is probably safer to store the mapping in a secure location than to use a breakable algorithm.

Re: Mask Data

Ronein — Sun, 16 Jul 2023 20:33:29 GMT

May you show an example for such alogirthm in sas code?

Re: Mask Data

SASKiwi — Sun, 16 Jul 2023 21:42:38 GMT

Here is how we do it using the SAS MD5 function. This produces a unique and reproducable key that can't be (easily) unencrypted so you need to keep a table of the original and encrypted keys to match back to.

data want;
  Cust_ID = '12345678';
  Cust_ID_Encrypted = put(md5(Cust_ID),$hex16.);
  put _all_;
run;

Re: Mask Data

Kurt_Bremser — Sun, 16 Jul 2023 21:53:45 GMT

You either need to keep

- a translation dataset

- the encryption/decryption code and the key

in a safe location. Both methods therefore provide the same level of security. Since a randomly created key mapping kept in a dataset is much easier to implement, use this method.

Re: Mask Data

Ronein — Mon, 17 Jul 2023 04:52:29 GMT

Sorry, you didnt create data sets-Real_Data and For_Import

Re: Mask Data

Ronein — Mon, 17 Jul 2023 04:56:37 GMT

Okay , however when I get back the data set then I want to have the ability to add back the real cust_ID.

How can I do it?

You didn't add this step here and it is critical step

Data tbl_have;
input cust_ID X Y Z;
cards;
33948398 10 20 30
54897733 11 12 13
98376333 40 60 80
;
run;

data want;
set tbl_have;
Cust_ID_Encrypted = put(md5(Cust_ID),$hex16.);
run;

Re: Mask Data

Ronein — Mon, 17 Jul 2023 05:08:10 GMT

I see that SHA256 and MD5 are for encryption without decryption.

I am looking for function that can do botyh encryption and decryption (So when I reciever the data set back I will be able to add the original customer ID)

Re: Mask Data

SASKiwi — Mon, 17 Jul 2023 05:42:35 GMT

@Ronein - When they send back their data with Cust_ID_Encrypted, just match it back to the Want dataset which has both the encrypted and unencrypted customer IDs.

Re: Mask Data

Kurt_Bremser — Mon, 17 Jul 2023 07:23:05 GMT

SAS does not provide a two-way encryption/decryption function like AES, so you would have to roll your own for an implementation of one such (reasonably secure) algorithm.

Using the RAND function and keeping a mapping dataset in a safe place (read: the location of the original data) works, is easy to implement, and does not violate data security/protection.

Re: Mask Data

Ronein — Mon, 17 Jul 2023 08:19:56 GMT

Thanks,

1- I suspect that using RAND function to generate fake customer ID can lead to duplicate problem.

Please remember that Customer ID should be unique value for each customer .

Then, using sequence number as fake customer number can be better.

What do you think?

2-I understand that in SAS there is no built in function that provide a two-way encryption/decryption.

My question is maybe anyone created such algorithm via SAS code ?

Re: Mask Data

Kurt_Bremser — Mon, 17 Jul 2023 08:51:16 GMT

Using a sequence weakens your encryption, as the original order becomes part of the encryption algorithm.

It also forces you to keep a mapping dataset, as the order may (will) change when new keys are inserted in your source data.

To prevent duplicates, you use the following method:

create the empty mapping dataset with two variables (original key, new key)
in the DATA step, read this dataset into two hash objects; one uses the original key as key, and the new key as data, the other uses the new key as key
for every observation, check if a new key is already defined in hash #1
if not found, create a new key with RAND in a DO UNTIL no match is found in hash #2; use the ADD method for both hashes
write to the output, omitting the original key
at the end of the step, save the contents of hash #1 back to the mapping dataset

When you receive data back, use the mapping dataset in a hash with new key as key and original key as data.

To your question #2: I seriously doubt anyone has gone to the length of creating a reliable two-way encryption method, and if, they'll want money for their work.

Re: Mask Data

Ksharp — Mon, 17 Jul 2023 11:44:45 GMT

Data have;
input cust_ID $;

length encode decode $ 200;
encode=put(cust_ID,$hex64.);
decode=input(encode,$hex64.);

cards;
33948398
54897733
98376333
;
run;
proc print;run;

Re: Mask Data

Patrick — Mon, 17 Jul 2023 12:12:01 GMT

I've developed some time ago a masking framework for masking PII columns in one environment and then transfer the masked tables to another environment (from PROD to DEV).

Important in this scenario was that all columns in the masked tables still had the same attributes (like data type and length). For this reason any approach using MD5, SHA etc. was not suitable.

In my case just using a sequence number for the masked data was no issue. And it's much easier to manage than using a random number because the moment you're generating the number randomly you need to also build a check that you haven't generated the same number already for another cleartext value. ...and because of the birthday paradigm the probability to do so is higher than one would intuitively assume.

If just using a sequence number the only thing you need to pay some attention to is to not use the algorithm on data that's sorted by the cleartext variable you want to mask (like a customer number that's often also "sequential").

To implement something fully dynamically that's also works for character variables with short length and alphanumerical strings takes a bit more but for your use case things can be kept rather simple.

It was for my use case important that the same cleartext value always resulted in the same masked values even when running the masking algorithm for multiple tables and tables from multiple months and at different times.

The only way this can work is to store and maintain a permanent masking lookup table with key-value pairs of cleartext and masked values.

Here what could work for you and meets all the above formulated requirements.

libname perm "%sysfunc(pathname(work))";

/* sample data */
data work.have;
  do cust_id=4,1,99999,10,1,5,4;
    obs_num+1;
    output;
  end;
run;

/* create permanent masking lookup table if not exists */
/* - key-value pairs of cleartext-masked values        */
%if %sysfunc(exist(perm.cust_mask)) ne 1 %then
  %do;
    data perm.cust_masklookup(index=;
      stop;
      set work.have(keep=cust_id);
      cust_id_masked=cust_id;
    run;
  %end;

/* create table with masked data */
data work.masked;
  if _n_=1 then 
    do;
      if 0 then set perm.cust_masklookup(rename=(cust_id_masked=__cust_id_masked));
      /* permanent masking lookup table with data masked in previous runs */
      dcl hash __h1(dataset:'perm.cust_masklookup(rename=(cust_id_masked=__cust_id_masked))');
      __h1.defineKey('cust_id');
      __h1.defineData('__cust_id_masked');
      __h1.defineDone();
      /* mask lookup table to collect with masked values created in this run */
      dcl hash __h1_new(dataset:'perm.cust_masklookup(obs=0 rename=(cust_id_masked=__cust_id_masked))');
      __h1_new.defineKey('cust_id');
      __h1_new.defineData('cust_id','__cust_id_masked');
      __h1_new.defineDone();

    end;
  call missing(of _all_);

  set work.have end=last;

  if __h1.find() ne 0 and __h1_new.find() ne 0 then
    do;
      __cust_id_masked=sum(__h1.num_items,__h1_new.num_items,76589);
      __rc=__h1_new.add(key:cust_id, data:cust_id, data:__cust_id_masked);
    end;
  cust_id=__cust_id_masked;

  /* write newly created key-value pairs of cleartext/masked values to work table */
  if last then __h1_new.output(dataset:'work.cust_masklookup_new(rename=(__cust_id_masked=cust_id_masked))');

  drop __:;

run;

/* append newly created masked values to permanent lookup table */
proc append base=perm.cust_masklookup data=work.cust_masklookup_new;
run;quit;

/* unmask values */
data work.un_masked;
  if _n_=1 then
    do;
      if 0 then set perm.cust_masklookup(rename=(cust_id_masked=__cust_id_masked));
      dcl hash h1(dataset:'perm.cust_masklookup(rename=(cust_id_masked=__cust_id_masked))');
      h1.defineKey('__cust_id_masked');
      h1.defineData('cust_id');
      h1.defineDone();
    end;
  call missing(of _all_);
  set work.masked;
  if h1.find(key:cust_id) ne 0 then 
    do;
      put 'Clear text key not found in masking lookup table';
      put cust_id=;
      abort;
    end;
  drop __:;
run;

/* print the various tables */
title 'PERM: cust_masklookup';
proc print data=perm.cust_masklookup_new;
run;
title 'have';
proc print data=work.have;
run;
title 'masked';
proc print data=work.masked;
run;
title 'un_masked';
proc print data=work.un_masked;
run;
title;

Please spend some time to understand above code before you start to ask questions about it.

Re: Mask Data

ErikLund_Jensen — Mon, 17 Jul 2023 14:50:01 GMT

Hi @Ronein

The SAS encryption algoritms will always generate the SAME KEY for a given value. This can be used in the following way:

1. Generate data to the external part with an encrypted key only.

2. Join your original and the returned data on the encrypted key, which you generate once more from the original ID in the join condition.

This way, you don't have and don't need a data set with both the original key and the encrypted key. I think that should satisfy all requirements.

* What you have;
data have;
  ID = 123456;
  Name = 'Ronein';
run;

* You make encrypted data to external part;
data xport; set have;
  drop ID;
  ID_encrypt = put(md5(cats(put(ID,12.))),$hex32.);
run;

* External part returns data with added information;
data return;
  set xport;
  Occupation = 'SAS Programmer';
run;

* You merge added infornmation onto orig data;
proc sql;
  create table want as
    select
      a.ID,
      a.Name,
      b.Occupation
    from have as a full outer join return as b
    on put(md5(cats(put(a.ID,12.))),$hex32.) = b.ID_encrypt;
quit;

Re: Mask Data

Patrick — Tue, 18 Jul 2023 11:31:48 GMT

@Ronein Just encoding via $hex64 doesn't cut it. Anyone who knows how you encoded the values can easily decode them.

But reading your original requirements again: So if "the other company" adds data (columns) to your table then they also need to know the customer. How else would they be capable to add the data to the right customer. If this is true then it's likely more about secure transmission of data and not about masking PII columns. Is that correct?

Re: Mask Data

Ronein — Tue, 18 Jul 2023 12:28:57 GMT

The real story is that by law it is forbidden for me to keep real customer id so i need to keep other fake value and my target is to be able to come back to real customer id ( from fake customer id)

Re: Mask Data

yabwon — Tue, 18 Jul 2023 13:08:11 GMT

[EDIT:] (after sending this I saw that @Tom already wrote that "no encrypting" answer)

The question was already answered but the following also satisfies your 1,2, and 3 requirements:

Data have;
input cust_ID someValue $;
cards;
33948398 A
54897733 B
98376333 C
54897733 D
;
run;

proc sort data=have(keep=cust_ID) out=lookup nodupkey;
  by cust_ID;
run;

data lookup;
  set lookup;
  masked_cust_ID + 1;
run;

proc print data=lookup;
run;

Since it is you who provides input data you have full control on the "masking" and no encryption function is needed.

All the best

Bart