Search code examples
sas

Conditional mean with replicated

suppose to have the following:

ID    Value
A      2.4     
A      2.3         
B      1.2
C      7.5
C      3.5
C      1.3
C      1.9

Is there a way to do the mean of "Value" all the times the ID is replicated (2 times or more) while maintaining the value corresponding to not replicated as it is?

Desired output:

ID    Value
A      2.35            
B      1.2
C      3.55

Thank you in advance

Solution

  • You can use Proc SQL and a statistical aggregate function.

    proc sql;
  create table want as
  select 
    id
  , mean(value) as value_mean
  , n(value) as id_count label='Number of values in id group'
  from have
  group by id
  ;

    The same results can be obtained using Proc MEANS

    proc means noprint nway data=have;
  class id;
  output out=want(drop=_:) mean(value)=value_mean n(value)=id_count;
run;