Hi Colleagues,
Attached is a small part of my huge dataset.
I need to identify the “worst ever” delinquency bucket each customer has ever fallen into.
Delinquency is defined as any value in the data set except “CURRENT”.
Answer:
Account worse ever delinquency bucket the customer has ever fallen
44 NPNA
99 1-30days
My Effort:
I reviewed the literature and found that this is a problem related to “working across variables” as opposed to “working across observations”.
I was manage only to develop a character array like below.
DATA want;
SET have;
ARRAY Amth_1(13) $ mth_1 - mth_13;
run;
Could any one of you help me.
Thanks
Mirisage
Some code like below should give you what you want.
proc format;
value $SeverityScore
'DWO' = 5
'NPNA' = 4
'90+DAYS' = 3
'31-90DAYS' = 2
'1-30DAYS' = 1
'CURRENT',' ' = 0
OTHER = 99
;
value SeverityText
5 = 'DWO'
4 = 'NPNA'
3 = '90+DAYS'
2 = '31-90DAYS'
1 = '1-30DAYS'
0 = 'CURRENT or blank'
99= 'undefined'
;
run;
data want(drop=_:);
set have;
array mth {*} mth_:;
format MaxSev SeverityText.;
do _i=1 to dim(mth);
MaxSev=max(MaxSev,input(put(upcase(strip(mth(_i))),$SeverityScore.),8.));
end;
run;
Some code like below should give you what you want.
proc format;
value $SeverityScore
'DWO' = 5
'NPNA' = 4
'90+DAYS' = 3
'31-90DAYS' = 2
'1-30DAYS' = 1
'CURRENT',' ' = 0
OTHER = 99
;
value SeverityText
5 = 'DWO'
4 = 'NPNA'
3 = '90+DAYS'
2 = '31-90DAYS'
1 = '1-30DAYS'
0 = 'CURRENT or blank'
99= 'undefined'
;
run;
data want(drop=_:);
set have;
array mth {*} mth_:;
format MaxSev SeverityText.;
do _i=1 to dim(mth);
MaxSev=max(MaxSev,input(put(upcase(strip(mth(_i))),$SeverityScore.),8.));
end;
run;
Since your dataset is huge and the maximum delinquency is not original information, it might be preferable to create a view instead of a new dataset. It can be done like this:
data want / view=want;
set SASForum.have;
array mth{13} mth_1-mth_13;
do _i = 1 to dim(mth);
_d = max(_d, indexc("Cc139NnDd", first(left(mth{_i}))));
end;
length mth_max $10;
mth_max = choosec(max(_d, 1),
" CURRENT", " CURRENT", " 1-30days", " 31-90days", " 90+days", " NPNA", " NPNA", " DWO", " DWO");
drop _i _d;
run;
PG
Hi PGStats,
This code works perfectly. Great! Thank you very much.
I ran this code and then examined the library where the file "want" was created. Yes, the file "want" was created not as a data set but as "something else" (which may be what we called
view).
Now I realized how to create a view and the difference between data set and view.
Thanks again!
Mirisage
Hi Patrick,
This code worked very well. This is great.
Thank you very much. Thanks again!
Mirisage
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.