Sum a column only when consecutive rows have same entries

[Rohit_R]

Hi,

I have a dataset with a by variable and a flag with 1 or 0 values. I want to sum only the first consecutive entries of 1 of the flag variable and output them.
Ex.

ID flag
A 0
A 1
A 1
A 0
B 1
B 0
B 0
C 1
C 1
C 1
C 0
C 1
C 1

The output dataset i want is:
ID. Count
A 2
B 1
C 3

I have tried using retain statement and conditional sum of when flag =1 but the count is incorrect for ID C from the example above.

Any help will be appreciated.

Thanks

[ballardw]

---

@Rohit_R wrote:
Hi,

I have a dataset with a by variable and a flag with 1 or 0 values. I want to sum only the first consecutive entries of 1 of the flag variable and output them.
Ex.

ID flag
A 0
A 1
A 1
A 0
B 1
B 0
B 0
C 1
C 1
C 1
C 0
C 1
C 1

The output dataset i want is:
ID. Count
A 2
B 1
C 3

I have tried using retain statement and conditional sum of when flag =1 but the count is incorrect for ID C from the example above.

Any help will be appreciated.

Thanks

---

Since retain and conditional summing is the likely approach then show the code you used.

Likely issues are not resetting the counter to 0 for the first value of the ID (hint) or not resetting/ setting a flag when done within a group of ID values

[novinosrin]

data have;
input ID $ flag;
datalines;
A 0
A 1
A 1
A 0
B 1
B 0
B 0
C 1
C 1
C 1
C 0
C 1
C 1
;

data want;
set have;
by  flag notsorted;
if first.flag and flag=1 then count=1;
else count+1;
if last.flag and flag=1 then do;output;count=0;end;
run;

proc sort data=want out=final_want(drop=flag) nodupkey;
by id flag;
run;

[Astounding]

It does present some logic challenges.  One way:

data want;

set have;

by ID flag notsorted;

retain c count;

if first.id then count=0;

if first.flag then c=0;

c + flag;

if last.flag and count=0 then count=c;

if last.id;

keep id count;

run;

[novinosrin · Accepted solution]

data have;
input ID $ flag;
datalines;
A 0
A 1
A 1
A 0
B 1
B 0
B 0
C 1
C 1
C 1
C 0
C 1
C 1
;

data want;
f=0;
do until(last.id);
set have;
by id flag notsorted;
if first.id then count=0;
if flag then count+1;
if last.flag and flag and  not f then do;output;f=1;end;
end;
drop f:;
run;

[Rohit_R]

Thank you, works like a charm!

[Ksharp]

data have;
input ID $ flag;
datalines;
A 0
A 1
A 1
A 0
B 1
B 0
B 0
C 1
C 1
C 1
C 0
C 1
C 1
;
proc summary data=have;
by id flag notsorted;
output out=temp;
run;
data want;
 set temp(where=(flag=1));
 by id;
 if first.id;
 keep id _freq_;
run;