BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elbarto
Obsidian | Level 7

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
Ksharp
Super User
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;

View solution in original post

4 REPLIES 4
elbarto
Obsidian | Level 7
Opps, I had made a typo, I fixed it now.
ChrisNZ
Tourmaline | Level 20

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
Super User
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 725 views
  • 0 likes
  • 3 in conversation