Hello Everyone,
I have question.
I am trying to find minimum value that was sustained for at least 8 consecutive observations. That minimum value should be the maximum value possible that continued for at least 8 sequential observation. I also want number of days.
Here is the dataset type and desired output-
ID | day | cigarettes |
1 | 1 | 8 |
1 | 2 | 10 |
1 | 3 | 15 |
1 | 4 | 13 |
1 | 5 | 16 |
1 | 6 | 12 |
1 | 7 | 12 |
1 | 8 | 15 |
1 | 9 | 16 |
1 | 10 | 14 |
1 | 11 | 10 |
1 | 12 | 9 |
1 | 13 | 3 |
1 | 14 | 20 |
1 | 15 | 14 |
1 | 16 | 23 |
1 | 17 | 21 |
2 | 1 | 12 |
2 | 2 | 10 |
2 | 3 | 7 |
2 | 4 | 17 |
2 | 5 | 15 |
2 | 6 | 14 |
2 | 7 | 15 |
2 | 8 | 14 |
2 | 9 | 17 |
2 | 10 | 18 |
2 | 11 | 19 |
2 | 12 | 18 |
2 | 13 | 9 |
Desired output -
id | Sustained minimum cigarettes | Sustained minimum days |
1 | 12 | 8 |
2 | 14 | 9 |
As we see for ID 1 had at least 12 cigarettes for 8 days( from day 3 to day 10) and ID had 15 cigarettes for 9 days(from day 4 to day 12).
Any suggestions on how to do it?
Your explanation confused me, but your numbers suggest you want:
Here is suggestion you can test against your data.
data want (keep=id highest_min_cig8 sustained);;
do n=1 by 1 until (last.id);
set have;
by id;
array cigvals {0:7} _temporary_; /*Most recent 8-day window of cigarette values*/
cigvals{mod(n,8)}=cigarettes;
if n>=8 then do;
min_cig8=min(of cigvals{*});
if min_cig8>highest_min_cig8 then do;
highest_min_cig8=min_cig8;
mmc8_beg=n-7;
mmc8_end=n;
end;
else if min_cig8=highest_min_cig8 then mmc8_end=mmc8_end+1;
end;
end;
sustained=mmc8_end+1-mmc8_beg;
run;
The highest value for ID 1 in the timespan 3 to 10 is 16, not 12. Similar for ID 2.
Your explanation confused me, but your numbers suggest you want:
Here is suggestion you can test against your data.
data want (keep=id highest_min_cig8 sustained);;
do n=1 by 1 until (last.id);
set have;
by id;
array cigvals {0:7} _temporary_; /*Most recent 8-day window of cigarette values*/
cigvals{mod(n,8)}=cigarettes;
if n>=8 then do;
min_cig8=min(of cigvals{*});
if min_cig8>highest_min_cig8 then do;
highest_min_cig8=min_cig8;
mmc8_beg=n-7;
mmc8_end=n;
end;
else if min_cig8=highest_min_cig8 then mmc8_end=mmc8_end+1;
end;
end;
sustained=mmc8_end+1-mmc8_beg;
run;
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.