Data Averaging and monotonic increase

[fwu811]

Hi,

I would like to do some simple averaging of the existing dataset by certain grouping and bucketing, but I also need to ensure that the value of each bucket increases as buckets always increase. 

For example,

data have;
input Col1 Col2;
datalines;
1 2
2 2
3 1
4 1
5 3

6 3

;

Data I want are;

<=2 2

<=4 2

<=6 3

Note that for the bucket with Col1 values between 2 and 4, the actual average is 1, which is less than the value (2) of the bucket for Col1 less than 2, so the final value for the 2nd bucket should be replaced with 2. 

Many thanks in advance.

[PaigeMiller]

I don't understand, you say "for Col1 values between 2 and 4" but your desired output has <=4, these are not the same. How are the limits for <=2 and <=4 and <=6 chosen, or are they fixed?

[s_lassen · Accepted solution]

One possibility is to use a format to create the buckets and then use PROC SUMMARY and finally a data step for correction:

proc format;
  value bucket 
    1-<3='2'
    3-<5='4'
    5-<7='6'
   ;
run;

proc summary nway data=have;
  class col1;
  var col2;
  format col1 bucket.;
  output out=avg(drop=_:) mean=;
run;

data want;
  set avg;
  retain _lastval;
  col2=max(col2,_lastval);
  _lastval=col2;
  col1=input(put(col1,bucket.),best32.);
  drop _:;
run;

I put in a line to replace the value of COL1 with the value displayed with the BUCKET. format, as that may make the data easier to work with afterwards. 

[fwu811]

It works and thanks.