How to identify incomplete rows and when incompleteness occurred in a dataset?

[Mirisage]

Hi Collogues,

I have the attached dataset.

Some customers have attrited the bank at different delinquency stages. For e.g.

• Account_num 11 has left the bank by 8^th month while at “current” stage.
• Account_num 55 left the bank by 5^th month while at “current” stage.
• Account 99 at 12^th month while he was at “1-30days” delinquency stage.

Question

I need to identify these 3 things.

1). which month each customer (those who left) left the bank and at what stage each left.

Answer:

Account            when left           stage when they left

11                     mth_8               current

55                     mth_5               current

99                     mth_12             1-30days

My approach:

I have first identified the missing value pattern using this.

data miss_pattern (drop=i);

  set b.have;

    array mychar(*) $ _character_;

  do i=1 to dim(mychar);

    if  mychar(i) ="" then mychar{i}=1;

      else mychar(i)=0;

  end;

run;

Then I have manually gone to each place and identified the stage at which the customer left the bank.

This is just like doing it manually.

Could any one of you help me with an efficient approach.

Thanks

Mirisage

[MikeZdeb]

hi ... using your data set (assumes no intervening empty months) ...

data want;

set have;

array mth_(13);

if missing(mth_13) then do;

   stage = coalescec(of mth_13 - mth_1);

   when_left = vname(mth_(14 - cmiss(of mth_(*))));

end;

run;

account_    when_

   num       left      stage

    11      mth_8      CURRENT

    22

    33

    44

    55      mth_5      CURRENT

  1000

    66

    77

    88

    99      mth_12    1-30days

[Mirisage]

Hi Mike,

Great!

This works pretty well and gives the correct answer.

Thank you very much.

Best regards

Mirisage