BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Where clause in datastep or sql?

data want;

     set have;

     where past_due=2;

run;

Linlin
Lapis Lazuli | Level 10

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
Super User
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
Lapis Lazuli | Level 10

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

Ksharp
Super User

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
Lapis Lazuli | Level 10

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
Super User

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
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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