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

[Ronein]

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

[Kurt_Bremser]

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

[Ronein]

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

[Kurt_Bremser]

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

[Kurt_Bremser]

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

[Ksharp]

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;