## Consecutive records

Regular Contributor
Posts: 178

# Consecutive records

Here is a sample output

loan_number                                             Past_Due_This_Month            Past_Due_Mar2014    Past_Due_Feb2014      Past_Due_Jan2014   Past_Due_Dec2013  and back 1 yr..........

1111                                                                     2                                         2
1122                                                                                                                2                                                                      2

1145                                                                   2                                                                                                                   2                          2

I need to keep records that have the months past due fields populated with a  minimum of 2  for a minimum of 2 consecutive months.  So in this case loans 1111 and 1145 would qualify. 1122 would be deleted.  How would I do this

Super User
Posts: 9,599

## Re: Consecutive records

Where clause in datastep or sql?

data want;

set have;

where past_due=2;

run;

Super Contributor
Posts: 1,636

## Re: Consecutive records

data have;

input id \$ a1-a5;

cards;

A 2 . 1 2 .

B 2 3 5 . .

C . 4 5 . .

D 2 . . . .

;

data want;

set have;

array a(*) a1-a5;

array b(*) \$ b1-b5  ;

do i=1 to dim(a);

if a(i) ge 2 then b(i)='a';

else b(i)='b';

end;

if find(cats(of b1-b5),'aa');

drop i b:;

run;

proc print;run;

Super User
Posts: 10,766

## Re: Consecutive records

```data have;
input id p1-p6 ;
cards;
1 2 3 2 2 3 2
2 3 4 2 3 2 3
3 3 4 3 5 2 4
4 3 4 5 2 2 2
;
run;
data want;
set have;
array a{*} p1-p6;
do i=1 to dim(a);
if a{i}=2 then do;
n=1;
do j=i+1 to dim(a);
if a{j]=2 then n+1;
else leave;
end;
end;
if n ge 2 then do;match=1;n=0;leave;end;
end;
if match then output;
run;

```

Xia Ke Shan

Regular Contributor
Posts: 178

## Re: Consecutive records

Lets revisit this with some real data

 ln_no d0 d1 d2 d3 d4 111 112 12/01/2013 113 01/01/2014 12/01/2013 11/01/2013 114 01/01/2014 115 10/01/2012 116 01/01/2014 01/01/2014 12/01/2013 117 12/01/2012 12/01/2012 12/01/2012 12/01/2012

So in this example, the fields are at the top. The d numbers represent months The rows represent dates.  So I need to keep ln_no 113 116 117 because they show a minimum of 3 months with dates consecutive.  So I begin my array and need assistance in the completion.

data want;

set want1;

array {*} d0-d6

do i=1 to dim(a);

Super User
Posts: 10,766

## Re: Consecutive records

If I understand what you mean.

```data have;
input id (p1-p4) (: mmddyy10.) ;
format p1-p4 mmddyy10.;
cards;
111     . . . .
112     12/01/2013     . . .
113     01/01/2014     12/01/2013     11/01/2013     .
114     01/01/2014     . . .
115     10/01/2012 . . .
116     01/01/2014     01/01/2014     12/01/2013     .
117     12/01/2012     12/01/2012     12/01/2012     12/01/2012
;
run;
data want;
set have;
array a{*} p1-p4;
do i=1 to dim(a);
n=1;
if missing(a{i}) then leave;
do j=i+1 to dim(a);
if missing(a{j}) then leave;

if  intnx('month',a{j-1},-1,'s') le a{j} le intnx('month',a{j-1},1,'s') then  n+1;
else leave;
end;
if n ge 2 then do;match=1;n=0;leave;end;
end;
if match then output;
run;

```

Xia Keshan

Message was edited by: xia keshan

Regular Contributor
Posts: 178

## Re: Consecutive records

Thanks for your code it helps.  However the code works if the first records are consecutive.  Here is your original example

111     . . . .

112     12/01/2013     . . .

113     01/01/2014     12/01/2013     11/01/2013    .

114     01/01/2014     . . .

115     10/01/2012 . . .

116     01/01/2014     01/01/2014     12/01/2013     .

117     12/01/2012     12/01/2012     12/01/2012     12/01/2012

so it kept the ones I have highlighted.  However the code needs to capture 3 consecutive at any point in the array.  What if we have these two scenarios.

112     12/01/2013     .                     10/15/2013

113     01/01/2014     12/01/2013     11/01/2013

114     02/11/2014                           05/15/2013                        01/15/2014

115     01/01/2013                            02/11/2013   02/15/2014    04/15/2014

Your code correctly keeps #113 however it also keeps 112 and 114.  Note those two are not 3 consecutive so I would want to eliminate those. I would also want to keep 115 because at some point it was consecutive

Super User
Posts: 10,766

## Re: Consecutive records

Did you mean at least three non-missing consecutive variables ?

```

data have;
input id (p1-p5) (: mmddyy10.) ;
format p1-p5 mmddyy10.;
cards;
111     . . . .     .
112     12/01/2013     . . . .
113     01/01/2014     12/01/2013     11/01/2013     . .
114     01/01/2014     . . .
115     10/01/2012 . . .
116     01/01/2014     01/01/2014     12/01/2013     .
117     12/01/2012     12/01/2012     12/01/2012     12/01/2012
118     12/01/2013     .    10/15/2013 . .
119     02/11/2014     .  05/15/2013   .   01/15/2014
120     01/01/2013     . 02/11/2013   02/15/2014    04/15/2014
;
run;
data want;
set have;
array a{*} p1-p5;
do i=1 to dim(a)-2;
if n(a{i},a{i+1},a{i+2})=3 then do;output;leave;end;
end;
run;

```

Xia Keshan

Posts: 3,167

## Re: Consecutive records

Here is another way of doing it, a bit wicked though

OPTIONS MISSING='|';

DATA HAVE;

INFILE CARDS TRUNCOVER;

INPUT ID (D1-D6) (:MMDDYY10.);

FORMAT D1-D6 MMDDYY10.;

CARDS;

112     12/01/2013     . .             10/15/2013

113     01/01/2014     12/01/2013     11/01/2013

114     02/11/2014 . .                           05/15/2013                        01/15/2014

115     01/01/2013   . .                  02/11/2013   02/15/2014 04/15/2014

;

DATA WANT;

SET HAVE;

LENGTH _CAT \$100;

_CAT = CATX('*',OF D1-D6);

IF PRXMATCH('/\d+\*\d+\*\d+/', _CAT);

DROP _CAT;

RUN;

OPTIONS MISSING=;

Regards,

Haikuo

Discussion stats
• 8 replies
• 409 views
• 3 likes
• 5 in conversation