topic Re: return the max value if the dates are progressing in SAS Programming

return the max value if the dates are progressing

u57961979 — Thu, 06 Oct 2022 08:19:14 GMT

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

Re: return the max value if the dates are progressing

PeterClemmensen — Thu, 06 Oct 2022 08:28:31 GMT

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

Re: return the max value if the dates are progressing

u57961979 — Thu, 06 Oct 2022 08:29:29 GMT

yes the date are all consecutive

Re: return the max value if the dates are progressing

PeterClemmensen — Thu, 06 Oct 2022 08:34:47 GMT

Do you have the flag variable already?

Re: return the max value if the dates are progressing

u57961979 — Thu, 06 Oct 2022 08:46:43 GMT

No i have only the date and the total

Re: return the max value if the dates are progressing

FreelanceReinh — Thu, 06 Oct 2022 10:48:19 GMT

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;

Re: return the max value if the dates are progressing

u57961979 — Thu, 06 Oct 2022 10:55:08 GMT

thank you so much