topic Re: looking for gaps in time spans for each memberID with multiple rows per memberID in New SAS User

looking for gaps in time spans for each memberID with multiple rows per memberID

abqdiane — Sun, 02 Dec 2018 00:51:17 GMT

Hello,

I have a list of members that can be enrolled in a club. Some members stay continuously enrolled, while other stop and start. I'm trying to find those members who stopped during the year and then re-enrolled and how long the gap is. Each time a member re-enrolls, a new record is created. So some members will only have one record, but other could have 4 or 5 records. In the example below, I have members for just one year, but the records go back for three to four years. Any suggestions? Would an array work? I thought about transpose, but there are over 80,000 records. I tried a retain function to come up with one row per member, but I couldn't get it to work.

From the example below ,member 111 has 12 continuous months, but member 122 has a one month gap.

I'd appreciate any help.

ID Start_Date End_Date
111 01/01/2017 12/31/2017
122 01/01/2017 06/30/2017
122 08/01/2017 12/31/2017
133 01/01/2017 06/30/2017
133 07/01/2017 12/31/2017
144 1/1/2017 02/28/2017
144 04/01/2017 06/30/2017
144 08/01/2017 10/31/2017
144 11/01/2017 12/31/2017

Re: looking for gaps in time spans for each memberID with multiple rows per memberID

Astounding — Sun, 02 Dec 2018 03:46:25 GMT

Assuming your data set is sorted as indicated, you could use:

data want;

set have;

by id;

gap_size = start_date - lag(end_date);

if first.id = 0 and gap_size > 1;

run;

This gives you only the observations that begin after a gap. If you want all the records, you could use a similar variation:

data want;

set have;

by id;

gap_size = start_time - lag(end_date);

if first.id then gap_size = 0;

run;

According to the examples you gave, there is no gap when gap_size is either 0 or 1.

Re: looking for gaps in time spans for each memberID with multiple rows per memberID

abqdiane — Tue, 04 Dec 2018 18:12:37 GMT

Thanks so much -