Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

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

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

Attachment

Accepted Solutions
Solution
‎06-23-2012 05:27 AM
Respected Advisor
Posts: 4,173

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

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=_Smiley Happy;
  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;

View solution in original post


All Replies
Solution
‎06-23-2012 05:27 AM
Respected Advisor
Posts: 4,173

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

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=_Smiley Happy;
  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;

Respected Advisor
Posts: 4,935

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

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

PG
Super Contributor
Posts: 338

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

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

Super Contributor
Posts: 338

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

Hi Patrick,

This code worked very well. This is great.

Thank you very much. Thanks again!

Mirisage


🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 335 views
  • 3 likes
  • 3 in conversation