Logic Help Required

[bnarang]

Hi

I have a very simple sorted data with observation as ID and Indicator, I would like to form a cluster for each ID with increments for each group of records. It should reset for every account type. I have thought a lot about it but no success so far. Please help . CLUSTER IS Required Output

ID         Indicator   Cluster

A11        .               .

A11        .               .

A11        1              1

A11        1              1

A11        1              1

A11        .

A11        .

A11        1              2

A11        1              2

A11        .

A11        1             3

A11        1             3

B11        .

B11        1             1

B11        1             1

B11        1             1

B11        .

B11        1             2

B11         .

B11        .

[Tom · Accepted solution]

Use BY processing with NOTSORTED option.  To zero out the CLUSTER number when indicator is missing you will need a second variable.

data have ;

input id $ indicator want;

cards;

A11 . .

A11 . .

A11 1 1

A11 1 1

A11 1 1

A11 . .

A11 . .

A11 1 2

A11 1 2

A11 . .

A11 1 3

A11 1 3

B11 . .

B11 1 1

B11 1 1

B11 1 1

B11 . .

B11 1 2

B11 . .

B11 . .

run;

data want ;

  set have ;

  by id indicator notsorted ;

  if first.id then ncluster=0;

  if first.indicator and not missing(indicator) then ncluster+1;

  if not missing(indicator) then cluster=ncluster;

  put id indicator want cluster;

  drop ncluster ;

run;

[bnarang]

Many Thanks Tom. Don't know the usage of NotSorted option but will read over the internet.

Thanks again