topic Re: Mask 4 last digits of credit card and apply it in UPDATE statement in SAS Programming

Mask 4 last digits of credit card and apply it in UPDATE statement

Ronein — Sun, 17 Sep 2023 14:29:52 GMT

Hello

Let's say that I have very big data set with 250 million rows and 30 columns.

I want to change the value in one char column.

This column contain only numeric digits but it is character because the meaning is last 4 digits of credit card.(Since it can contain zero's then better to store it as char column).

The change of value should be done for all rows (No need where clause) and should be done in following way:

Add one to each digit.

For example:

4732 will be 5843

9340 will be 0451 (Please note that 9 will become 0)

I know the way how to do it from other post

Data have;
input  MEZAHE_PART $;
cards;
0009
7100
1040
1323
1417
1610
4732
9340
;
Run;

data want;
set have;
do i = 1 to length(mezahe_part);
  if substr(mezahe_part,i,1) = "9"
  then substr(mezahe_part,i,1) = " ";
  else substr(mezahe_part,i,1) = put(sum(1,input(substr(mezahe_part,i,1),1.)),1.);
end;
drop i;
run;

My question- How can I do it using UPDATE statement instead of SET statement?

The DATA step UPDATE operation can read and write data in a single step so it is good advanatage.

I think that using UPDATE statement will be much more efficient.

I know the general form of UPDATE statement:

proc sql;

UPDATE Have

SET mezahe_part=.............

;

quit;

But the question is if there is a way to apply the code of calculate the new value here

Re: Mask 4 last digits of credit card and apply it in UPDATE statement

Kurt_Bremser — Sun, 17 Sep 2023 17:24:52 GMT

Don't bother. No matter what you do, the SAS system will have to read and write all dataset pages anyway.

Re: Mask 4 last digits of credit card and apply it in UPDATE statement

Ronein — Sun, 17 Sep 2023 18:54:59 GMT

So there is no advance of doing it using update statement?

Re: Mask 4 last digits of credit card and apply it in UPDATE statement

Kurt_Bremser — Mon, 18 Sep 2023 05:42:42 GMT

The SQL UPDATE is only better when you update a (very) small subset which is found through an index or because of sorting.

Re: Mask 4 last digits of credit card and apply it in UPDATE statement

Kurt_Bremser — Mon, 18 Sep 2023 06:19:36 GMT

PS the DATA step UPDATE statement has a different function, it updates values in one dataset with values from another dataset.

Re: Mask 4 last digits of credit card and apply it in UPDATE statement

Ksharp — Mon, 18 Sep 2023 11:30:51 GMT

Data have;
input  MEZAHE_PART $;
cards;
0009
7100
1040
1323
1417
1610
4732
9340
;
Run;

option cmplib=_null_;
proc fcmp outlib=work.func.math;
function xx(x $) $ 4;
 length encode $ 4;
 do i=1 to lengthn(x);
   encode=cats(encode,byte(rank(  ifc(char(x,i)='9','/',char(x,i))  ) + 1));
 end;
return (encode);
endsub;
run;

option cmplib=work.func varlenchk=nowarn;
proc sql;
update have
 set MEZAHE_PART=xx(MEZAHE_PART) ;
quit;