Consecutive Data

[novinosrin]

Thank you @thb  Here you go, sure I can optimize and make it efficient, however i have to run to catch the train to get home. I hope this meets your requirement. 

data have;
input ID	Appt $	Year	Month;
cards;
1	A	2014	10
1	A	2014	11
1	A	2014	12
1	B	2015	1
1	B	2015	2
1	B	2015	3
1	B	2015	4
2	A	2014	10
2	A	2014	11
2	A	2014	12
2	A	2015	1
2	A	2015	2
2	B	2016	1
2	B	2016	2
2	B	2016	3
2	B	2016	4
;

data want;
set have;
by id appt ;
if first.appt then Consecutive=0;
if dif(month)=1 or (dif(month) ne 1 and month=1 and lag(month)=12) then Consecutive+1;
else Consecutive=1;
run;

/*want final*/
proc sql;
create table want_final(drop=m) as
select *
from 
(select *,max(Consecutive) as m
from want
group by id)
group by id,appt
having count(*)=m
order by id,appt,consecutive;
quit;

[thb]

Hi novinosrin,

Thank you so much for your help.  I am almost there.  Unfortunately the code provided for the final table is not pulling all of the data that I need.  

The code you provided is giving me the correct consecutive count below in Table 1:

Table 1:

ID	Appt	Year	Month	Consecutive
1	A	2014	10	1
1	A	2014	11	2
1	A	2014	12	3
1	A	2015	1	4
1	A	2015	2	5
1	A	2015	3	6
1	A	2015	4	7
2	B	2015	1	1
2	B	2015	2	2
2	B	2015	3	3
2	B	2015	4	4
2	B	2015	5	5
2	B	2015	6	6
2	B	2015	7	7
2	B	2015	8	8

However, when I run the code for *want final*, I am only getting the data below in Table 2:

Table 2:

ID	Appt	Year	Month	Consecutive
2	B	2015	1	1
2	B	2015	2	2
2	B	2015	3	3
2	B	2015	4	4
2	B	2015	5	5
2	B	2015	6	6
2	B	2015	7	7
2	B	2015	8	8

I need both Appt A and B from Table 1.

[novinosrin · Accepted solution]

Are you sure?

I justreran the same code and i got the below results:

ID Appt Year Month Consecutive

1 B 2015 1 1
1 B 2015 2 2
1 B 2015 3 3
1 B 2015 4 4
2 A 2014 10 1
2 A 2014 11 2
2 A 2014 12 3
2 A 2015 1 4
2 A 2015 2 5

data have;
input ID	Appt $	Year	Month;
cards;
1	A	2014	10
1	A	2014	11
1	A	2014	12
1	B	2015	1
1	B	2015	2
1	B	2015	3
1	B	2015	4
2	A	2014	10
2	A	2014	11
2	A	2014	12
2	A	2015	1
2	A	2015	2
2	B	2016	1
2	B	2016	2
2	B	2016	3
2	B	2016	4
;

data want;
set have;
by id appt ;
if first.appt then Consecutive=0;
if dif(month)=1 or (dif(month) ne 1 and month=1 and lag(month)=12) then Consecutive+1;
else Consecutive=1;
run;

/*want final*/
proc sql;
create table want_final(drop=m) as
select *
from 
(select *,max(Consecutive) as m
from want
group by id)
group by id,appt
having count(*)=m
order by id,appt,consecutive;
quit;

proc print noobs;run;

[novinosrin]

And those results match with your desired results in the question

You wrote-

"Then, ideally I would only want the final table with the highest consecutive count:

ID	Appt	Year	Month	Consecutive
1	B	2015	1	1
1	B	2015	2	2
1	B	2015	3	3
1	B	2015	4	4
2	A	2014	10	1
2	A	2014	11	2
2	A	2014	12	3
2	A	2015	1	4
2	A	2015	2	5

My results:

ID Appt Year Month Consecutive

1 B 2015 1 1
1 B 2015 2 2
1 B 2015 3 3
1 B 2015 4 4
2 A 2014 10 1
2 A 2014 11 2
2 A 2014 12 3
2 A 2015 1 4
2 A 2015 2 5

[thb]

Hi novinosrin,

For some reason, when I'm running that code for *want final* table, it's dropping my data that has less consecutive appts.  For example, it only captures appt A and not appt B.

[Ksharp]

data have;
input ID	Appt $	Year	Month;
cards;
1	A	2014	10
1	A	2014	11
1	A	2014	12
1	B	2015	1
1	B	2015	2
1	B	2015	3
1	B	2015	4
2	A	2014	10
2	A	2014	11
2	A	2014	12
2	A	2015	1
2	A	2015	2
2	B	2016	1
2	B	2016	2
2	B	2016	3
2	B	2016	4
;
data month;
 do m=1 to 12;
  output;
 end;
run;
proc sql;
create table temp as
 select a.*,b.month,missing(b.month) as group
  from (select * from (select distinct ID,Appt,Year from have),(select m from month)) as a
   left join
  (select * from have) as b 
   on a.ID=b.ID and a.Appt=b.Appt and a.Year=b.Year and a.m=b.month
    order by 1,2,3,4;
quit;
data temp1;
 set temp;
 by ID Appt group notsorted;
 if first.group then count=0;
 count+1;
 if not missing(month);
 drop m group;
run;
proc sql;
create table want as
 select *
  from temp1
   where catx(' ',id,Appt) in
    (select catx(' ',id,Appt) from temp1 group by id having count=max(count));
quit;