topic Re: Consecutive Data in New SAS User

Consecutive Data

thb — Wed, 26 Sep 2018 21:57:42 GMT

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!

Re: Consecutive Data

novinosrin — Wed, 26 Sep 2018 22:19:40 GMT

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;

Re: Consecutive Data

thb — Wed, 26 Sep 2018 22:23:04 GMT

Hi novinosrin.

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

Re: Consecutive Data

novinosrin — Wed, 26 Sep 2018 22:27:21 GMT

does that solve the first problem?

Re: Consecutive Data

thb — Wed, 26 Sep 2018 22:28:48 GMT

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

Re: Consecutive Data

novinosrin — Wed, 26 Sep 2018 23:17:23 GMT

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;

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;

Re: Consecutive Data

Reeza — Wed, 26 Sep 2018 22:41:22 GMT

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!

Re: Consecutive Data

thb — Wed, 26 Sep 2018 22:57:43 GMT

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.

Re: Consecutive Data

thb — Wed, 26 Sep 2018 23:04:45 GMT

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.

Re: Consecutive Data

novinosrin — Wed, 26 Sep 2018 23:07:46 GMT

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

Re: Consecutive Data

novinosrin — Wed, 26 Sep 2018 23:14:21 GMT

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;

Re: Consecutive Data

thb — Wed, 26 Sep 2018 23:22:39 GMT

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

Re: Consecutive Data

novinosrin — Wed, 26 Sep 2018 23:23:12 GMT

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;

Re: Consecutive Data

novinosrin — Wed, 26 Sep 2018 23:25:41 GMT

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;

Re: Consecutive Data

thb — Wed, 26 Sep 2018 23:54:17 GMT

Hi,

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

Re: Consecutive Data

novinosrin — Thu, 27 Sep 2018 00:02:17 GMT

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;

Re: Consecutive Data

thb — Thu, 27 Sep 2018 02:24:22 GMT

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.

Re: Consecutive Data

novinosrin — Thu, 27 Sep 2018 02:34:56 GMT

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;

Re: Consecutive Data

novinosrin — Thu, 27 Sep 2018 02:40:49 GMT

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

Re: Consecutive Data

thb — Thu, 27 Sep 2018 11:47:01 GMT

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.