How to identify the “worst ever” delinquency band each customer ever fallen?

[Mirisage]

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”.

• The worst delinquency band is “DWO” (not found in this sample data set but I have in my huge data set, so need to code this too)
• Next worst is “NPNA”
• Next worst is “90+days” (not found in this sample data set but I have in my huge data set, so need to code this too)
• Next worst is “31-90days”
• Least worse is “1-30days”

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

[Patrick · Accepted solution]

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;

[PGStats]

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

[Mirisage]

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

[Mirisage]

Hi Patrick,

This code worked very well. This is great.

Thank you very much. Thanks again!

Mirisage