## Logic Help Required

Solved
Frequent Contributor
Posts: 86

# Logic Help Required

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        .

Accepted Solutions
Solution
‎07-02-2014 03:47 PM
Super User
Posts: 8,089

## Re: Logic Help Required

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;

All Replies
Solution
‎07-02-2014 03:47 PM
Super User
Posts: 8,089

## Re: Logic Help Required

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;

Frequent Contributor
Posts: 86

## Re: Logic Help Required

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

Thanks again

🔒 This topic is solved and locked.