topic Re: Consecutive records in SAS Procedures

Consecutive records

Q1983 — Tue, 08 Apr 2014 13:59:01 GMT

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

Re: Consecutive records

RW9 — Tue, 08 Apr 2014 15:00:24 GMT

Where clause in datastep or sql?

data want;

set have;

where past_due=2;

run;

Re: Consecutive records

Linlin — Tue, 08 Apr 2014 15:14:28 GMT

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;

Re: Consecutive records

Ksharp — Tue, 08 Apr 2014 15:22:03 GMT

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

Re: Consecutive records

Q1983 — Wed, 23 Apr 2014 20:12:32 GMT

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

Re: Consecutive records

Ksharp — Thu, 24 Apr 2014 02:59:48 GMT

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

Re: Consecutive records

Q1983 — Thu, 15 May 2014 14:45:51 GMT

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

Re: Consecutive records

Ksharp — Fri, 16 May 2014 12:58:06 GMT

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

Re: Consecutive records

Haikuo — Thu, 22 May 2014 16:13:24 GMT

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