BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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

 

 

5 REPLIES 5
Ronein
Meteorite | Level 14
So there is no advance of doing it using update statement?
Ksharp
Super User
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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1081 views
  • 1 like
  • 3 in conversation