Hello,
I am having trouble getting the highest count of consecutive data for my dataset. Here's an example of my data:
ID | Appt | Year | Month |
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 |
The code I am using is below, but I am not getting the results that I want. Since an ID can have multiple consecutive appointment times, I need to only take the highest consecutive count. With the code below, I am getting the following data:
ID | Appt | Year | Month | Consecutive |
1 | A | 2014 | 10 | 1 |
1 | A | 2014 | 11 | 2 |
1 | A | 2014 | 12 | 3 |
1 | B | 2015 | 1 | 4 |
1 | B | 2015 | 2 | 5 |
1 | B | 2015 | 3 | 6 |
1 | B | 2015 | 4 | 7 |
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 |
2 | B | 2016 | 1 | 6 |
2 | B | 2016 | 2 | 7 |
2 | B | 2016 | 3 | 8 |
2 | B | 2016 | 4 | 9 |
However, I want the following table to be produced:
ID | Appt | Year | Month | Consecutive |
1 | A | 2014 | 10 | 1 |
1 | A | 2014 | 11 | 2 |
1 | A | 2014 | 12 | 3 |
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 |
2 | B | 2016 | 1 | 1 |
2 | B | 2016 | 2 | 2 |
2 | B | 2016 | 3 | 3 |
2 | B | 2016 | 4 | 4 |
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 |
data TestD (KEEP = ID Appt Year Month Consecutive);
SET TestC;
BY ID Appt;
RETAIN Consecutive;
IF First.ID or First.Appt THEN Consecutive = 0;
ELSE Consecutive = Consecutive +1;
IF Consecutive GT 0 THEN OUTPUT;
run;
Thank you for your help in advance!
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;
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;
if 0 then set have;
do Consecutive=1 by 1 until(last.appt);
set have;
by id appt;
output;
end;
run;
Hi novinosrin.
Thank you for your response, but unfortunately that does not give me the data that I need.
does that solve the first problem?
No, the main problem is trying to get a true consecutive count by ID, Appt, Year, and Month.
I wonder why my understanding is so poor,
are you after this?
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 Year Month;
if dif(month) ne 1 then Consecutive=1;
else Consecutive+1;
run;
or
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;
Hi novinosrin,
Not quite. So the code that you've provided is counting the data as consectuive every time it's a new year, but I want the data to be counted when it's a consecutive month regardless of the year.
I think this is prolly more closer?
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 dif(month)=1 or (dif(month) ne 1 and month=1 and lag(month)=12) then Consecutive+1;
else Consecutive=1;
run;
Hello,
Yes, this is the consecutive count that I want, however I only want to keep the data for the ID with the highest consecutive count.
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
It's so close - any idea on how to get rid of the over observations?
Thank you
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;
date = mdy(month, 1, year);
prev_date = mdy(lag(month), 1, lag(year));
if first.id then do;
consecutive=0;
prev_date=.;
end;
if intck('month', date, prev_date) > 1 then consecutive=1;
else consecutive+1;
run;
@thb wrote:
Hello,
I am having trouble getting the highest count of consecutive data for my dataset. Here's an example of my data:
ID Appt Year Month 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
The code I am using is below, but I am not getting the results that I want. Since an ID can have multiple consecutive appointment times, I need to only take the highest consecutive count. With the code below, I am getting the following data:
ID Appt Year Month Consecutive 1 A 2014 10 1 1 A 2014 11 2 1 A 2014 12 3 1 B 2015 1 4 1 B 2015 2 5 1 B 2015 3 6 1 B 2015 4 7 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 2 B 2016 1 6 2 B 2016 2 7 2 B 2016 3 8 2 B 2016 4 9
However, I want the following table to be produced:
ID Appt Year Month Consecutive 1 A 2014 10 1 1 A 2014 11 2 1 A 2014 12 3 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 2 B 2016 1 1 2 B 2016 2 2 2 B 2016 3 3 2 B 2016 4 4
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
data TestD (KEEP = ID Appt Year Month Consecutive); SET TestC; BY ID Appt; RETAIN Consecutive; IF First.ID or First.Appt THEN Consecutive = 0; ELSE Consecutive = Consecutive +1; IF Consecutive GT 0 THEN OUTPUT; run;
Thank you for your help in advance!
Hi Reeza,
Unfortunately, this gives me the same issue where I am getting a consecutive count all the way through instead of counting the highest consecutive count data.
This seems to match your 1st result
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;
Can you confirm the just previous, or here again. if yes, 2nd part is no biggie
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;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.