data want;
set have;
by byvar;
retain x1_running_max;
if first.byvar
then x1_running_max = x1;
else x1_running_max = max(x1_running_max,x1);
run;
Straightforward, easy to understand, and less time- and resource-consuming than anything you might do in SQL.
Maxim 14!!!
Just for illustration, a comparison of the DATA step method with the one SQL suggestion:
69 data have (drop=i j);
70 do i = 1 to 1000;
71 do j = 0 to 100;
72 byvar=i;
73 ind=j;
74 x1=sin(j/10)+i;
75 y1=cos(j/10)+i;
76 output;
77 end;
78 end;
79 run;
NOTE: The data set WORK.HAVE has 101000 observations and 4 variables.
NOTE: Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 1931.21k
OS Memory 22180.00k
Timestamp 19.09.2025 03:00:09 nachm.
Step Count 62 Switch Count 2
Page Faults 0
Page Reclaims 165
Page Swaps 0
Voluntary Context Switches 9
Involuntary Context Switches 2
Block Input Operations 0
Block Output Operations 6408
80
81 data want;
82 set have;
83 by byvar;
84 retain x1_running_max;
85 if first.byvar
86 then x1_running_max = x1;
87 else x1_running_max = max(x1_running_max,x1);
88 run;
NOTE: There were 101000 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 101000 observations and 5 variables.
NOTE: Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
real time 0.01 seconds
user cpu time 0.02 seconds
system cpu time 0.00 seconds
memory 3625.37k
OS Memory 24488.00k
Timestamp 19.09.2025 03:00:09 nachm.
Step Count 63 Switch Count 2
Page Faults 0
Page Reclaims 519
Page Swaps 0
Voluntary Context Switches 13
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 7944
89
90 proc sql;
91 create table want as
92 select a.*, max(b.x1) as x1_running_max
93 from have a, have b
94 where a.byvar=b.byvar & b.ind<=a.ind
95 group by 1,2,3,4;
NOTE: Table WORK.WANT created, with 101000 rows and 5 columns.
96 quit;
NOTE: Verwendet wurde: PROZEDUR SQL - (Gesamtverarbeitungszeit):
real time 1.50 seconds
user cpu time 3.00 seconds
system cpu time 0.27 seconds
memory 459314.90k
OS Memory 481956.00k
Timestamp 19.09.2025 03:00:11 nachm.
0.01 seconds vs. 1.5 seconds. 'Nuff said.
... View more
