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
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;
Are the dates actually consecutive? Meaning, there are no gaps in the dates, correct?
Do you have the flag variable already?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.