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
Where clause in datastep or sql?
data want;
set have;
where past_due=2;
run;
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;
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
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);
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
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.