Help using Base SAS procedures

Consecutive records

Reply
Frequent Contributor
Posts: 141

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
Super User
Posts: 7,942

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,023

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

Frequent Contributor
Posts: 141

Re: Consecutive records

Lets revisit this with some real data

ln_nod0d1d2d3d4
111
11212/01/2013
11301/01/201412/01/201311/01/2013
11401/01/2014
11510/01/2012
11601/01/201401/01/201412/01/2013
11712/01/201212/01/201212/01/201212/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,023

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

Frequent Contributor
Posts: 141

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,023

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

Respected Advisor
Posts: 3,156

Re: Consecutive records

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

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

Ask a Question
Discussion stats
  • 8 replies
  • 388 views
  • 3 likes
  • 5 in conversation