topic Re: How to identify incomplete rows and when incompleteness occurred in a dataset? in SAS Procedures

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

Mirisage — Fri, 22 Jun 2012 20:25:44 GMT

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

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

MikeZdeb — Fri, 22 Jun 2012 21:02:47 GMT

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

55 mth_5 CURRENT

1000

99 mth_12 1-30days

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

Mirisage — Sun, 24 Jun 2012 17:43:38 GMT

Hi Mike,

Great!

This works pretty well and gives the correct answer.

Thank you very much.

Best regards

Mirisage