How do I create a variable set to maximum consecutive values

[itmightbeharry]

Hello everyone:

I'm using SAS 9.4. I have a data set for which I have written a variable identifying a time when an id (participant) had a fever (temp greater than or equal to 100.4). I have also added a variable to count the number of consecutive days of fever by id. What I would like help doing is creating a new variable, say "duration", with a value set to the maximum consecutive days of fever for a given id. I then want to perform proc means on the maximum consecutive value, duration, by the class group. If anyone could help with this, it would be very much appreciated. I have copied the code I have thus far, along with my proc print output and the output I'd like to obtain.

data work.fever;
  set cert.tempdata;
  if temp>=100.4 then fever=1;
    else if temp<100.4 then fever=0;
run;
data work.consec;
  set work.fever;
  by id;
  if first.id or fever=0 then consecutive=0;
  if fever=1 then consecutive+1;
run;
proc print data=work.consec;
run;

Here's what I have thus far:

Obs temp id time group fever consecutive123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100

98.421	1	1	1	0	0
98.670	1	2	1	0	0
94.854	1	3	1	0	0
95.244	1	4	1	0	0
96.902	1	5	1	0	0
99.505	1	6	1	0	0
98.492	1	7	1	0	0
99.668	1	8	1	0	0
97.695	1	9	1	0	0
99.321	1	10	1	0	0
97.579	2	1	1	0	0
97.398	2	2	1	0	0
96.013	2	3	1	0	0
101.626	2	4	1	1	1
97.522	2	5	1	0	0
96.997	2	6	1	0	0
96.139	2	7	1	0	0
97.730	2	8	1	0	0
99.007	2	9	1	0	0
99.842	2	10	1	0	0
97.990	3	1	1	0	0
99.603	3	2	1	0	0
100.728	3	3	1	1	1
98.932	3	4	1	0	0
97.081	3	5	1	0	0
101.121	3	6	1	1	1
99.821	3	7	1	0	0
98.182	3	8	1	0	0
100.347	3	9	1	0	0
95.729	3	10	1	0	0
99.843	4	1	1	0	0
94.984	4	2	1	0	0
97.375	4	3	1	0	0
97.954	4	4	1	0	0
95.892	4	5	1	0	0
98.724	4	6	1	0	0
94.150	4	7	1	0	0
95.499	4	8	1	0	0
100.850	4	9	1	1	1
94.260	4	10	1	0	0
96.835	5	1	1	0	0
99.437	5	2	1	0	0
98.385	5	3	1	0	0
99.185	5	4	1	0	0
100.123	5	5	1	0	0
97.317	5	6	1	0	0
97.983	5	7	1	0	0
99.006	5	8	1	0	0
101.173	5	9	1	1	1
96.844	5	10	1	0	0
101.163	6	1	2	1	1
98.637	6	2	2	0	0
101.504	6	3	2	1	1
101.505	6	4	2	1	2
99.638	6	5	2	0	0
103.026	6	6	2	1	1
105.390	6	7	2	1	2
98.695	6	8	2	0	0
102.922	6	9	2	1	1
102.736	6	10	2	1	2
101.730	7	1	2	1	1
102.496	7	2	2	1	2
102.005	7	3	2	1	3
97.683	7	4	2	0	0
99.773	7	5	2	0	0
100.150	7	6	2	0	0
101.544	7	7	2	1	1
100.596	7	8	2	1	2
99.482	7	9	2	0	0
103.363	7	10	2	1	1
101.314	8	1	2	1	1
102.871	8	2	2	1	2
100.924	8	3	2	1	3
102.355	8	4	2	1	4
100.699	8	5	2	1	5
105.151	8	6	2	1	6
100.762	8	7	2	1	7
100.286	8	8	2	0	0
101.314	8	9	2	1	1
101.578	8	10	2	1	2
103.964	9	1	2	1	1
104.265	9	2	2	1	2
102.314	9	3	2	1	3
101.910	9	4	2	1	4
97.880	9	5	2	0	0
100.609	9	6	2	1	1
101.778	9	7	2	1	2
101.542	9	8	2	1	3
101.187	9	9	2	1	4
101.071	9	10	2	1	5
99.531	10	1	2	0	0
99.955	10	2	2	0	0
101.370	10	3	2	1	1
100.399	10	4	2	0	0
101.481	10	5	2	1	1
100.807	10	6	2	1	2
96.331	10	7	2	0	0
102.101	10	8	2	1	1
103.946	10	9	2	1	2
101.036	10	10	2	1	3

This is the output I'm attempting to obtain:

[itmightbeharry]

I realized after posting by SAS Output did not reproduce as intended. Hope this works better for everyone.

Obs	temp	id	time	group	fever	consecutive
1	98.421	1	1	1	0	0
2	98.670	1	2	1	0	0
3	94.854	1	3	1	0	0
4	95.244	1	4	1	0	0
5	96.902	1	5	1	0	0
6	99.505	1	6	1	0	0
7	98.492	1	7	1	0	0
8	99.668	1	8	1	0	0
9	97.695	1	9	1	0	0
10	99.321	1	10	1	0	0
11	97.579	2	1	1	0	0
12	97.398	2	2	1	0	0
13	96.013	2	3	1	0	0
14	101.626	2	4	1	1	1
15	97.522	2	5	1	0	0
16	96.997	2	6	1	0	0
17	96.139	2	7	1	0	0
18	97.730	2	8	1	0	0
19	99.007	2	9	1	0	0
20	99.842	2	10	1	0	0
21	97.990	3	1	1	0	0
22	99.603	3	2	1	0	0
23	100.728	3	3	1	1	1
24	98.932	3	4	1	0	0
25	97.081	3	5	1	0	0
26	101.121	3	6	1	1	1
27	99.821	3	7	1	0	0
28	98.182	3	8	1	0	0
29	100.347	3	9	1	0	0
30	95.729	3	10	1	0	0
31	99.843	4	1	1	0	0
32	94.984	4	2	1	0	0
33	97.375	4	3	1	0	0
34	97.954	4	4	1	0	0
35	95.892	4	5	1	0	0
36	98.724	4	6	1	0	0
37	94.150	4	7	1	0	0
38	95.499	4	8	1	0	0
39	100.850	4	9	1	1	1
40	94.260	4	10	1	0	0
41	96.835	5	1	1	0	0
42	99.437	5	2	1	0	0
43	98.385	5	3	1	0	0
44	99.185	5	4	1	0	0
45	100.123	5	5	1	0	0
46	97.317	5	6	1	0	0
47	97.983	5	7	1	0	0
48	99.006	5	8	1	0	0
49	101.173	5	9	1	1	1
50	96.844	5	10	1	0	0
51	101.163	6	1	2	1	1
52	98.637	6	2	2	0	0
53	101.504	6	3	2	1	1
54	101.505	6	4	2	1	2
55	99.638	6	5	2	0	0
56	103.026	6	6	2	1	1
57	105.390	6	7	2	1	2
58	98.695	6	8	2	0	0
59	102.922	6	9	2	1	1
60	102.736	6	10	2	1	2
61	101.730	7	1	2	1	1
62	102.496	7	2	2	1	2
63	102.005	7	3	2	1	3
64	97.683	7	4	2	0	0
65	99.773	7	5	2	0	0
66	100.150	7	6	2	0	0
67	101.544	7	7	2	1	1
68	100.596	7	8	2	1	2
69	99.482	7	9	2	0	0
70	103.363	7	10	2	1	1
71	101.314	8	1	2	1	1
72	102.871	8	2	2	1	2
73	100.924	8	3	2	1	3
74	102.355	8	4	2	1	4
75	100.699	8	5	2	1	5
76	105.151	8	6	2	1	6
77	100.762	8	7	2	1	7
78	100.286	8	8	2	0	0
79	101.314	8	9	2	1	1
80	101.578	8	10	2	1	2
81	103.964	9	1	2	1	1
82	104.265	9	2	2	1	2
83	102.314	9	3	2	1	3
84	101.910	9	4	2	1	4
85	97.880	9	5	2	0	0
86	100.609	9	6	2	1	1
87	101.778	9	7	2	1	2
88	101.542	9	8	2	1	3
89	101.187	9	9	2	1	4
90	101.071	9	10	2	1	5
91	99.531	10	1	2	0	0
92	99.955	10	2	2	0	0
93	101.370	10	3	2	1	1
94	100.399	10	4	2	0	0
95	101.481	10	5	2	1	1
96	100.807	10	6	2	1	2
97	96.331	10	7	2	0	0
98	102.101	10	8	2	1	1
99	103.946	10	9	2	1	2
100	101.036	10	10	2	1	3

[PeterClemmensen · Accepted solution]

See if you can use this as a template

data have;
input temp id time group fever;
datalines;
98.421 1 1 1 0 0
98.670 1 2 1 0 0
94.854 1 3 1 0 0
95.244 1 4 1 0 0
96.902 1 5 1 0 0
99.505 1 6 1 0 0
98.492 1 7 1 0 0
99.668 1 8 1 0 0
97.695 1 9 1 0 0
99.321 1 10 1 0 0
97.579 2 1 1 0 0
97.398 2 2 1 0 0
96.013 2 3 1 0 0
101.626 2 4 1 1 1
97.522 2 5 1 0 0
96.997 2 6 1 0 0
96.139 2 7 1 0 0
97.730 2 8 1 0 0
99.007 2 9 1 0 0
99.842 2 10 1 0 0
97.990 3 1 1 0 0
99.603 3 2 1 0 0
100.728 3 3 1 1 1
98.932 3 4 1 0 0
97.081 3 5 1 0 0
101.121 3 6 1 1 1
99.821 3 7 1 0 0
98.182 3 8 1 0 0
100.347 3 9 1 0 0
95.729 3 10 1 0 0
99.843 4 1 1 0 0
94.984 4 2 1 0 0
97.375 4 3 1 0 0
97.954 4 4 1 0 0
95.892 4 5 1 0 0
98.724 4 6 1 0 0
94.150 4 7 1 0 0
95.499 4 8 1 0 0
100.850 4 9 1 1 1
94.260 4 10 1 0 0
96.835 5 1 1 0 0
99.437 5 2 1 0 0
98.385 5 3 1 0 0
99.185 5 4 1 0 0
100.123 5 5 1 0 0
97.317 5 6 1 0 0
97.983 5 7 1 0 0
99.006 5 8 1 0 0
101.173 5 9 1 1 1
96.844 5 10 1 0 0
101.163 6 1 2 1 1
98.637 6 2 2 0 0
101.504 6 3 2 1 1
101.505 6 4 2 1 2
99.638 6 5 2 0 0
103.026 6 6 2 1 1
105.390 6 7 2 1 2
98.695 6 8 2 0 0
102.922 6 9 2 1 1
102.736 6 10 2 1 2
101.730 7 1 2 1 1
102.496 7 2 2 1 2
102.005 7 3 2 1 3
97.683 7 4 2 0 0
99.773 7 5 2 0 0
100.150 7 6 2 0 0
101.544 7 7 2 1 1
100.596 7 8 2 1 2
99.482 7 9 2 0 0
103.363 7 10 2 1 1
101.314 8 1 2 1 1
102.871 8 2 2 1 2
100.924 8 3 2 1 3
102.355 8 4 2 1 4
100.699 8 5 2 1 5
105.151 8 6 2 1 6
100.762 8 7 2 1 7
100.286 8 8 2 0 0
101.314 8 9 2 1 1
101.578 8 10 2 1 2
103.964 9 1 2 1 1
104.265 9 2 2 1 2
102.314 9 3 2 1 3
101.910 9 4 2 1 4
97.880 9 5 2 0 0
100.609 9 6 2 1 1
101.778 9 7 2 1 2
101.542 9 8 2 1 3
101.187 9 9 2 1 4
101.071 9 10 2 1 5
99.531 10 1 2 0 0
99.955 10 2 2 0 0
101.370 10 3 2 1 1
100.399 10 4 2 0 0
101.481 10 5 2 1 1
100.807 10 6 2 1 2
96.331 10 7 2 0 0
102.101 10 8 2 1 1
103.946 10 9 2 1 2
101.036 10 10 2 1 3
;

data want;
   do until (last.id);
      set have;
      by group id;
      if fever = 0 then consecutive = 0;
      if fever     then consecutive + 1;
      maxc = max(maxc, consecutive);
   end;
   consecutive=0;
run;

proc means data = want nway maxdec=2;
   class group;
   var maxc;
run;

[itmightbeharry]

Thank you very much for your help draycut! I decided to forego the "work.consec" data I had written, instead simply using "work.fever". Everything worked great! Thank you again. Big help!

If it helps anyone later, here's the code I used and output:

title 'Summary of Number of Days with Longest Consecutive Fever by Group';
data work.fever;
  set cert.tempdata;
  if temp>=100.4 then fever=1;
    else if temp<100.4 then fever=0;
run;
data work.duration;
  do until (last.id);
    set work.fever;
	by id;
	if fever = 0 then consecutive = 0;
	if fever then consecutive+1;
	duration = max(duration, consecutive);
  end;
  consecutive=0;
  label duration='# days with longest consecutive fever';
run;
proc means data = work.duration nway maxdec=1 nonobs n mean std min median max;
  class group;
  var duration;
run;

The MEANS Procedure

Analysis Variable : duration # days with longest consecutive fever
treatment group	N	Mean	Std Dev	Minimum	Median	Maximum
1	5	0.8	0.4	0.0	1.0	1.0
2	5	4.0	2.0	2.0	3.0	7.0