Find rolling max from today up to start day

[hhchenfx]

Hi,

My data has ID, date, Value. For each Id, at a given date, I want to find the Max value up to that date.

My code below works but I wonder if there is any shorter/faster way, such as using proc expand.

Thanks,

HHC

data have;
input id date value;
datalines;
1 1 2
1 2 15
1 3 6
1 4 17
1 5 8
1 6 9
2 1 60
2 2 50
2 3 600
;run;
proc sort data=have; by id descending date; run;

data want;
set have nobs=nobs;
drop i j _: exit;
i+1;
max_value=0;
exit=0;
do j=i to nobs until (exit=1);
	set have (keep=id date value rename=(id=_id date = _date value=_value)) point=j;
	if id^=_id then do;exit=1;leave; end;
	if max_value<_value then max_value=_value;

end;
run;

[PaigeMiller · Accepted solution]

---

@hhchenfx wrote:

My data has ID, date, Value. For each Id, at a given date, I want to find the Max value up to that date.

My code below works but I wonder if there is any shorter/faster way, such as using proc expand.

---

What an odd question. The first thing you should do is look at the documentation for PROC EXPAND.

However, this much simpler DATA step will work:

data want;
    set have;
    by id;
    retain maxvalue;
    if first.id then maxvalue=value;
    else if value>maxvalue then maxvalue=value;
run;