Count number of 0's until next 1

[elbarto]

I have dataset similar to the following:

DATA have;
input id	day	shock	status

;
DATALINES;
1001	33	.	1
1001	34	1	0
1001	35	.	0
1001	36	.	0
1001	37	.	0
1001	38	.	1
1001	39	.	1
1001	40	.	1
1001	41	.	0
1001	42	.	0
1005	55	.	1
1005	56	.	1
1005	57	.	1
1005	58	.	1
1005	59	1	0
1005	60	.	1
1005	61	.	1


;
RUN;

For each id, I want to count the number of times that status takes on a value of zero starting from the day where shock=1 until the next time that status=1. Note that in the data, when shock=1, status always takes on a value of 0.

The resultant data should be:

DATA want;
input id	count
;
DATALINES;
1001	4
1005	1
;
RUN;

For example, for id=1001, when shock=1 on day 34, status=0 until day 38 when status becomes 1. There are 4 days on which status=0, so count=4 in the "want" dataset. Note that on day 41, status also equals to 0 but there is no value of 1 for shock, so these zeros are not counted.

[ChrisNZ]

Why is COUNT=1 for ID=1005?

[elbarto]

Opps, I had made a typo, I fixed it now.

[ChrisNZ]

Your code doesn't run. Please always vet it by pasting it back in SAS.

This does your count:

data WANT;
  set HAVE;
  by ID;
  if first.ID then call missing(START,COUNT);
  if SHOCK=1 then START+1;
  if START & ^STATUS then COUNT+1;
  if STATUS then START=0;
  if last.ID;
run;

[Ksharp · Accepted solution]

DATA have;
input id	day	shock	status;
DATALINES;
1001	33	.	1
1001	34	1	0
1001	35	.	0
1001	36	.	0
1001	37	.	0
1001	38	.	1
1001	39	.	1
1001	40	.	1
1001	41	.	0
1001	42	.	0
1005	55	.	1
1005	56	.	1
1005	57	.	1
1005	58	.	1
1005	59	1	0
1005	60	.	1
1005	61	.	1
;
RUN;
data temp;
 set have;
 by id;
 if first.id or shock=1 or status ne lag(status) then group+1;
run;
proc sql;
create table want as
select id ,sum(status=0) as count
 from temp
  where group in (select group from temp where shock=1)
   group by id;
quit;