@JJP1 wrote:
thanks @Kurt_Bremser ,it is setting whole table but i need to update single column.i tried below option and it is working thanks.
proc sql;
update t
set ydate= tdate-1 where ydate<> tdate-1
;
quit;
which is an inefficient solution.
See this:
data have;
do i = 1 to 1000000;
tdate = rand('integer','01jan2000'd,today());
if mod(i,3) = 0
then ydate = tdate - 1;
else ydate = rand('integer','01jan2000'd,today());
output;
end;
format tdate ydate yymmddd10.;
run;
data want1;
set have;
ydate = tdate - 1;
run;
proc sql;
update have
set ydate = tdate - 1 where ydate <> tdate - 1
;
quit;
and this log from it:
73 data have;
74 do i = 1 to 1000000;
75 tdate = rand('integer','01jan2000'd,today());
76 if mod(i,3) = 0
77 then ydate = tdate - 1;
78 else ydate = rand('integer','01jan2000'd,today());
79 output;
80 end;
81 format tdate ydate yymmddd10.;
82 run;
NOTE: The data set WORK.HAVE has 1000000 observations and 3 variables.
NOTE: Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
real time 2.75 seconds
cpu time 2.74 seconds
83
84 data want;
85 set have;
86 ydate = tdate - 1;
87 run;
NOTE: There were 1000000 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT1 has 1000000 observations and 3 variables.
NOTE: Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
real time 0.22 seconds
cpu time 0.23 seconds
88
89 proc sql;
90 update have
91 set ydate = tdate - 1 where ydate <> tdate - 1
92 ;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: 666568 rows were updated in WORK.HAVE.
93 quit;
NOTE: Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
real time 6.37 seconds
cpu time 6.35 seconds
As you can see, the data step performs better by at least an order of magnitude. The result is the same, of course.
... View more