DATA Step, Macro, Functions and more

Logic Help Required

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 86
Accepted Solution

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
Super User
Posts: 7,043

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;

View solution in original post


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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 212 views
  • 0 likes
  • 2 in conversation