return the max value if the dates are progressing

[u57961979]

Hello, I need your help.
I have to find all the values that have a total over 1 million, but if the dates are progressive, and there are, for example, 3 consecutive dates with a value over 1 million, I have to select only the date with the highest total.
in the attached example, it should output as output:
dates: 02/01/2000 and 06/01/2000

[PeterClemmensen]

Are the dates actually consecutive? Meaning, there are no gaps in the dates, correct?

[u57961979]

yes the date are all consecutive

[PeterClemmensen]

Do you have the flag variable already?

[u57961979]

No i have only the date and the total

[FreelanceReinh · Accepted solution]

Hello @u57961979,

---

@u57961979 wrote:
No i have only the date and the total

---

If you don't need the flag as a variable, you can create a format that provides the same information and then use it together with the GROUPFORMAT option of the BY statement in a DATA step:

proc format;
value flag
low -1e6  = '0'
1e6<-high = '1';
run;

data want(drop=d t m);
do until(t>1e6 & last.t);
  set have(rename=(date=d total=t));
  by t groupformat notsorted;
  format t flag.;
  if t>1e6 & t>m then do;
    m=t;
    date=d;
    total=t;
  end;
end;
format date ddmmyy10. total commax16.2;
run; 

[u57961979]

thank you so much