Obsidian | Level 7

## Count number of 0's until next 1

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Count number of 0's until next 1

``````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;``````
4 REPLIES 4
Tourmaline | Level 20

## Re: Count number of 0's until next 1

Why is COUNT=1 for ID=1005?

Obsidian | Level 7

## Re: Count number of 0's until next 1

Tourmaline | Level 20

## Re: Count number of 0's until next 1

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

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

Super User

## Re: Count number of 0's until next 1

``````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;``````
Discussion stats
• 4 replies
• 725 views
• 0 likes
• 3 in conversation