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.
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;
Why is COUNT=1 for ID=1005?
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.