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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.