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
Don't bother. No matter what you do, the SAS system will have to read and write all dataset pages anyway.
The SQL UPDATE is only better when you update a (very) small subset which is found through an index or because of sorting.
PS the DATA step UPDATE statement has a different function, it updates values in one dataset with values from another dataset.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.