Consecutive records

[Q1983]

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

[RW9]

Where clause in datastep or sql?

data want;

     set have;

     where past_due=2;

run;

[Linlin]

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;

[Ksharp]

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

[Q1983]

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);

[Ksharp]

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

[Q1983]

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

[Ksharp]

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

[Haikuo]

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