topic Data Averaging and monotonic increase in SAS Programming

Data Averaging and monotonic increase

fwu811 — Wed, 10 Jan 2024 09:59:16 GMT

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.

Re: Data Averaging and monotonic increase

PaigeMiller — Wed, 10 Jan 2024 10:41:03 GMT

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?

Re: Data Averaging and monotonic increase

s_lassen — Wed, 10 Jan 2024 15:05:12 GMT

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.

Re: Data Averaging and monotonic increase

fwu811 — Tue, 14 May 2024 11:30:33 GMT

It works and thanks.