re: maximum by group

[twildone]

Hi....I am trying to find the maximum frequency grouped by ID and either the same Year or consecutive Years, That is, when the difference in Years is more than 1, then the group by ID and Year is a new group. This is the part that I am stuck on. Any suggestions. Thanks. 

data have;
infile datalines dlm=',';
input ID $ Year $ Freq;
datalines; 
1,2015,2
1,2016,5
1,2016,8
1,2018,6
1,2019,4
1,2019,3
1,2020,7
1,2020,3
2,2018,1
2,2018,3
2,2020,4
2,2021,6
;

data want;
infile datalines dlm=',';
input ID $ Year $ Freq max_Freq;
datalines; 
1,2015,2,8
1,2016,5,8
1,2016,8,8
1,2018,6,7
1,2019,4,7
1,2019,3,7
1,2020,7,7
1,2020,3,7
2,2018,1,3
2,2018,3,3
2,2020,4,6
2,2021,6,6
;

[novinosrin · Accepted solution]

data have;
infile datalines dlm=',';
input ID $ Year  Freq;
datalines; 
1,2015,2
1,2016,5
1,2016,8
1,2018,6
1,2019,4
1,2019,3
1,2020,7
1,2020,3
2,2018,1
2,2018,3
2,2020,4
2,2021,6
;

data temp/view=temp;
 set have;
 by id;
 if first.id then group=1;
 if dif(year)>1 then group+1;
run;

proc sql;
  create table want as
  select id, year, freq, max(freq) as max_freq
  from temp
  group by id, group;
quit ;

[twildone]

Hi Novinsorin….it worked...thanks for your Help.