topic Re: SAS/SQL, how to getting rolling max with rolling and changing window-length?! in SAS Programming

SAS/SQL, how to getting rolling max with rolling and changing window-length?!

hellohere — Fri, 19 Sep 2025 10:52:36 GMT

data temp(drop=i j);
do i=1 to 3;
	do j=0 to 100;
    	byvar=i;
        ind=j;
        x1=sin(j/10)+i;
        y1=cos(j/10)+i;
       	output;
  	end;
end;
run;quit;

/*proc sgplot data=temp;
	by byvar;
	series x=ind y=x1/lineattrs=(color=red thickness=2 pattern=solid);  
	series x=ind y=y1/y2axis lineattrs=(color=blue thickness=2 pattern=solid);  
run;quit;*/

proc sql;
create table temp_new as
SELECT byvar, ind, x1, y1,
       MAX(x1) OVER(ORDER BY byvar ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
       AS x1_running_max
FROM temp
group by byvar;
quit;

I need get the max from the first row to CURRENT ROW. Tried the code above. But it complains. Anyway to do it in SQL?! Surely in dataset with byvar-retain it is doable. I prefer in SAS/SQL.

Re: SAS/SQL, how to getting rolling max with rolling and changing window-length?!

quickbluefish — Fri, 19 Sep 2025 11:45:42 GMT

PROC SQL doesn't allow these types of 'windowing' functions, unfortunately (not sure about FEDSQL). In a DATA step, you can do something like the following:

proc sort data=have; by id; run;

data want;
set have;
by id;
RETAIN x1_running_max y1_running_max;
if first.id then call missing(x1_running_max, y1_running_max);
x1_running_max=max(x1, x1_running_max);
y1_running_max=max(y1, y1_running_max);
run;

Re: SAS/SQL, how to getting rolling max with rolling and changing window-length?!

LinusH — Fri, 19 Sep 2025 11:39:06 GMT

Unfortunately SAS SQL does not support window functions.

And I can't see how to solve this in ANSI 1992 SQL, at least easily.

The data step is probably your best option here.

Re: SAS/SQL, how to getting rolling max with rolling and changing window-length?!

quickbluefish — Fri, 19 Sep 2025 11:46:43 GMT

just edited - forgot you were trying to do this within each level of ID.

Re: SAS/SQL, how to getting rolling max with rolling and changing window-length?!

FreelanceReinh — Fri, 19 Sep 2025 12:37:40 GMT

Hi @hellohere,

@hellohere wrote:
I prefer in SAS/SQL.

Then try this:

proc sql;
create table want as
select a.*, max(b.x1) as x1_running_max
from temp a, temp b
where a.byvar=b.byvar & b.ind<=a.ind
group by 1,2,3,4;
quit;

Re: SAS/SQL, how to getting rolling max with rolling and changing window-length?!

Tom — Fri, 19 Sep 2025 13:53:48 GMT

Why would it matter if you used PROC SQL or something else?

You should check if you have SAS/ETS licensed.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/etsug/etsug_expand_overview.htm

Re: SAS/SQL, how to getting rolling max with rolling and changing window-length?!

Kurt_Bremser — Fri, 19 Sep 2025 15:03:23 GMT

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.

Re: SAS/SQL, how to getting rolling max with rolling and changing window-length?!

hellohere — Sat, 20 Sep 2025 06:00:00 GMT

Thanks all.

The dataset is just for example. I prefer SQL because DO-With-Dataset is kind of tedious in term of coding, may

I say this.

Re: SAS/SQL, how to getting rolling max with rolling and changing window-length?!

Ksharp — Sat, 20 Sep 2025 07:42:01 GMT

PROC SQL can not handle BIG table for your question.

And @FreelanceReinh already gave you the Cartesian Product Solution. Here I want to present sub-query solution.

data temp(drop=i j);
do i=1 to 3;
do j=0 to 100;
byvar=i;
ind=j;
x1=sin(j/10)+i;
y1=cos(j/10)+i;
output;
end;
end;
stop;
run;

proc sql;
create table want as
select a.*,
(select max(x1) from temp where byvar=a.byvar and ind <= a.ind) as x1_running_max,
(select max(y1) from temp where byvar=a.byvar and ind <= a.ind) as y1_running_max
 from temp as a;
quit;

Re: SAS/SQL, how to getting rolling max with rolling and changing window-length?!

Tom — Sun, 21 Sep 2025 14:35:52 GMT

Everyone has their preferences. SQL syntax is very good for somethings. But in general it is extremely verbose and much more cumbersome that DATA step code.

Take your example:

create table temp_new as
SELECT byvar, ind, x1, y1,
       MAX(x1) OVER(ORDER BY byvar ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
       AS x1_running_max
FROM temp
group by byvar;

In a data step that is just:

data want2;
  set temp;
  by byvar;
  if first.byvar then x1_running_max=x1;
  else x1_running_max=max(x1_running_max,x1);
  retain x1_running_max;
run;

So instead of having the say "create table...as...from" you just need to say "data...set...". Instead of "group by" you just need "by". Plus you don't have to put COMMAS between the names in the list if more than one BY variable is need. Instead you can just use spaces.

And SQL does not support variable lists.