topic Re: How to read consecutive occurrence of counts in SAS Programming

How to read consecutive occurrence of counts

hjjijkkl — Tue, 05 Jan 2021 18:30:00 GMT

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.

Re: How to read consecutive occurrence of counts

Astounding — Tue, 05 Jan 2021 18:37:06 GMT

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.

Re: How to read consecutive occurrence of counts

ballardw — Tue, 05 Jan 2021 18:39:48 GMT

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

Re: How to read consecutive occurrence of counts

novinosrin — Tue, 05 Jan 2021 18:40:23 GMT


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;

Re: How to read consecutive occurrence of counts

hjjijkkl — Tue, 05 Jan 2021 19:38:09 GMT

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

Re: How to read consecutive occurrence of counts

hjjijkkl — Tue, 05 Jan 2021 19:52:52 GMT

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

Re: How to read consecutive occurrence of counts

novinosrin — Tue, 05 Jan 2021 21:12:20 GMT

@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;

Re: How to read consecutive occurrence of counts

ballardw — Tue, 05 Jan 2021 21:49:09 GMT

@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.

Re: How to read consecutive occurrence of counts

Ksharp — Wed, 06 Jan 2021 12:19:30 GMT

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;

Re: How to read consecutive occurrence of counts

Astounding — Wed, 06 Jan 2021 14:01:02 GMT

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;