SAS: Compute mean by grouping variable excluding observation

Question

Thanks in advance for any and all help here.

An example data set and desired output is linked below.

I want to compute new variables from the "Have" data set as follows: RE: the average of "R" values within a given "Cat" variable value excluding the specific observation IE the average of "I" responses within a given "Cat" variable value excluding the specific observation

Answer 1

You can readily do this by joining in the values and some arithmetic:

proc sql;
    select t.*,
           (sumr - r) / (cnt - 1) as re,
           (sumi - i) / (cnt - 1) as ie
    from t left join
         (select cat, count(*) as cnt, sum(r) as sumr, sum(i) as sumi
          from t
          group by cat
          having count(*) > 1
         ) tt
         on t.cat = tt.cat;

Notice that I used a left join and having clause to prevent division by zero.