Sequential minimum sustained values

[ScoobyDO]

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?

[Kurt_Bremser]

The highest value for ID 1 in the timespan 3 to 10 is 16, not 12. Similar for ID 2.

[ScoobyDO]

Hello Kurt, We are looking at the minimum value which is maximum of all the smaller values for 8 days, that was consistent from day 3 to day 10. In other words for that period there were no values less than 12 for ID one. ID 1 had at least 12 cigarettes for 8 days.

[mkeintz · Accepted solution]

Your explanation confused me, but your numbers suggest you want:

1. Track, for each rolling 8-day windows, the minimum cigarette consumption
2. Over all such 8-day windows keep the maximum of those 8-day minumums
3. For that maximum, which must start out with an 8-day length, see how much further it might last.

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;

[ScoobyDO]

This works!! Thank you