Solved: Re: Consecutive Data

thb · Posted 09-26-2018 05:56 PM

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!

novinosrin · Posted 09-26-2018 10:34 PM

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;

View solution in original post

novinosrin · Posted 09-26-2018 06:19 PM

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;

thb · Posted 09-26-2018 06:23 PM

Hi novinosrin.

Thank you for your response, but unfortunately that does not give me the data that I need.

novinosrin · Posted 09-26-2018 06:27 PM

does that solve the first problem?

thb · Posted 09-26-2018 06:28 PM

No, the main problem is trying to get a true consecutive count by ID, Appt, Year, and Month.

novinosrin · Posted 09-26-2018 06:34 PM

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;

thb · Posted 09-26-2018 07:04 PM

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.

novinosrin · Posted 09-26-2018 07:13 PM

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;

thb · Posted 09-26-2018 07:22 PM

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.

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

Reeza · Posted 09-26-2018 06:41 PM

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!

thb · Posted 09-26-2018 06:57 PM

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.

novinosrin · Posted 09-26-2018 07:07 PM

Hi @Reeza, Sorry for the bother and very embarrassing to ask. I haven't understood the requirement well enough and rather the task seems far too simple. Have you got it? If yes, when you post, please include a couple of notes 😞

@thb Hang in there

novinosrin · Posted 09-26-2018 07:23 PM

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;

novinosrin · Posted 09-26-2018 07:25 PM

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;

thb · Posted 09-26-2018 07:54 PM

Hi,

Yes, the first part is correct. Thank you! Now I just need the second part.

Ready to join fellow brilliant minds for the SAS Hackathon?