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

Super Contributor
Posts: 338

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

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 8th month while at “current” stage.
• Account_num 55 left the bank by 5th month while at “current” stage.
• Account 99 at 12th 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.

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

Valued Guide
Posts: 765

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

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

Super Contributor
Posts: 338

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

Hi Mike,

Great!

This works pretty well and gives the correct answer.

Thank you very much.

Best regards

Mirisage

Discussion stats
• 2 replies
• 242 views
• 1 like
• 2 in conversation