CODEWITHSUNDEEP
sasmaxrowsretain
Aga
Aga

Reputation: 3

SAS maximum value in preceding rows

I need to calculate max (Measure) in the last 3 months for each ID and month, without using PROC SQL.I was wondering I could do this using the RETAIN statement, however I have no idea how to implement the condition of comparing the value of Measure in the current row and the preceding two.

I will also need to prepare the above for more than 3 months so any solution that do not require a separate step for each additional month would be absolutely appreciated!

Here is the data I have:

data have;
input month ID $ measure;
cards;
201501 A 0
201502 A 30
201503 A 60
201504 A 90
201505 A 0
201506 A 0
201501 B 0
201502 B 30
201503 B 0
201504 B 30
201505 B 60
;

Here the one I need:

data want;
input month ID $ measure max_measure_3m;
cards;
201501 A 0 0
201502 A 30 30
201503 A 60 60  
201504 A 90 90
201505 A 0 90
201506 A 0 90
201501 B 0 0
201502 B 30 30
201503 B 0 30
201504 B 30 30
201505 B 60 60
;

And here both tables: the one I have on the left and the one I need on the right

Upvotes: 0

Views: 728

Answers (2)

Reeza
Reeza

Reputation: 21274

You can do this with an array that's size to your moving window. I'm not sure what type of dynamic code you need in terms of windows. If you need the max for a 4 or 5 month on top of 3 month then I would recommend using PROC EXPAND instead of these methods. The documentation for PROC EXPAND has a good example of how to do this.

data want;
    set have;
    by id;
    array _prev(0:2) _temporary_;

    if first.id then
        do;
            call missing (of _prev(*));
            count=0;
        end;
    count+1;
    _prev(mod(count, 3))=measure;
    max=max(of _prev(*));
    drop count;
run;

proc expand data=test out=out method=none;
  by id;
  id month;

  convert x = x_movave3 / transformout=(movave 3);
  convert x = x_movave4 / transformout=(movave 4);
 run;

Upvotes: 1

DomPazz
DomPazz

Reputation: 12465

Try this:

data want(drop=l1 l2 cnt tmp);
set have;
by id;
retain cnt max_measure_3m l1 l2;

if first.id then do;
    max_measure_3m = 0;
    cnt = 0;
    l1 = .;
    l2 = .;
end;

cnt = cnt + 1;

tmp = lag(measure);
if cnt > 1 then
    l1 = tmp;

tmp = lag2(measure);
if cnt > 2 then
    l2 = tmp;

if measure > l1 and measure > l2 then
    max_measure_3m = measure;
run;

Upvotes: 0

Related Questions