topic re: maximum by group in SAS Programming

re: maximum by group

twildone — Wed, 13 Apr 2022 20:45:32 GMT

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
;

Re: re: maximum by group

novinosrin — Wed, 13 Apr 2022 20:55:22 GMT



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 ;

Re: re: maximum by group

twildone — Wed, 13 Apr 2022 21:18:12 GMT

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