How to read consecutive occurrence of counts

[hjjijkkl]

Hello;

I have this kind of data 

Data have;

input id value count;

record;

1 2 1

1 8 1

1 12 0

2 35 0

2 15 0

2 3 1

3 11 0

3 5 1

3 4 1

4 72 0

4 8 1

4 67 0

I want an the outcome table to look like this

id value

1 2 

1 8 

3 5 

3 4 

I want to count only two consecutive 1's in "count" variable. 

[Astounding]

Here's a way.  Note that I'm taking your question literally.  If there are three consecutive occurrences, they will be ignored.

data want;
   totcount=0;
   do until (last.id);
      set have;
      by id;
      totcount + count;
   end;
   do until (last.id);
      set have;
      by id;
      if totcount=2 then output;
   end;
   drop totcount;
run;

If you really meant to say "2 or more" consecutive 1's, then change the comparison to read:

if totcount >= 2 then output;

This logic assumes that 0 and 1 are the only possible values for COUNT.

[ballardw]

So what would you want if the data includes more than consecutive counts of 1 than 2? The first two, first and last, last two or something else? Or multiple sets of 2 (or more) consecutive?

Your example was not very complete as to possible inputs, so you need to provide rules for processing something like the following or state explicitly that the cases mentioned will never ever occur and not come back with questions when they do.

Such as:

Data have;

input id value count;

record;

1 2 1

1 8 1

1 10 1       Inserted to make additional consecutive as example.

1 12 0

1 13 1       Inserted to make additional consecutive as example.

1 18 1       Inserted to make additional consecutive as example.

2 35 0

2 15 0

2 3 1

3 11 0

3 5 1

3 4 1

4 72 0

4 8 1

4 67 0

[hjjijkkl]

the example you provided above is very good. I still want the outcome to look like this. which is having the first 2 consecutive 1's.
id value

1 2

1 8

3 5

3 4

[novinosrin]

Data have;

input id value count;

cards;

1 2 1

1 8 1

1 12 0

2 35 0

2 15 0

2 3 1

3 11 0

3 5 1

3 4 1

4 72 0

4 8 1

4 67 0
;

data want;
 set have;
 by id count notsorted;
 if not(first.count and last.count);
 if count;
 drop count;
run;
 

[hjjijkkl]

thank you.
If there is more than 2 consecutive 1's but, if I want the outcome to have only the first 2 consecutive. how can I program that?
exmaple;

Data have;

input id value count;

record;

1 2 1

1 8 1

1 10 1 Inserted to make additional consecutive as example.

1 12 0

1 13 1 Inserted to make additional consecutive as example.

1 18 1 Inserted to make additional consecutive as example.

2 35 0

2 15 0

2 3 1

3 11 0

3 5 1

3 4 1

4 72 0

4 8 1

4 67 0

I want an the outcome table to look like this

id value

1 2

1 8

3 5

3 4

[novinosrin · Accepted solution]

@hjjijkkl  Oh you changed the requirement. Hmm

data want;
 do _n_=1 by 1 until(last.count);
  set have;
  by id count notsorted;
  if first.id then do; _c1=0;_c=0;end;
  if first.count and count then _c1+1;
  if count then _c+1;
  else _c=0;
 end;
 do _n_=1 to _n_;
  set have;
  if count and _c1=1 and _c>=2 and _n_<=2 then output;
 end;
 drop _: count;
run;

[ballardw]

---

@novinosrin wrote:

@hjjijkkl  Oh you changed the requirement. Hmm

Not so much a change of requirement as a clarification. I suspected the data OP presented might be too "clean" or simple and asked about clarification of requirement for more complex data. Seems like my question may have struck a chord or two with the full data in question.

[Ksharp]

Just for fun.

Data have;
input id value count;
cards;
1 2 1
1 8 1
1 10 1
1 12 0
1 13 1
1 18 1
2 35 0
2 15 0
2 3 1
3 11 0
3 5 1
3 4 1
4 72 0
4 8 1
4 67 0
;

data want;
 set have;
 by id count notsorted;
 if first.id then g=0;
 g+(first.count and count=1);
 if first.count then n=0;
 n+1;
 if not (first.count and last.count) and g=1 and n lt 3 and count;
 drop n g;
run;
 

[Astounding]

My first answer was so pathetic, I need a mulligan.

data want;
   set have;
   by id count notsorted;
   if first.id then found=0;
   retain found;
   if count=1;
   if first.count and last.count=0 and found=0;
   found = _n_;
   do _n_=_n_ to _n_ + 1;
      set have point=_n_;
      output;
   end;
   drop found;
run;