Geomean

[Daily1]

/* Create the initial dataset */
data have;
  input S_ID ID C_ID M_ID P_ID CUPRICE BPRICE rel;
  datalines;
1 497 48 110 4 50 47.87 104.4495509
1 498 51 113 4 10 8.6 116.2790698
1 498 52 115 4 10 8.87 112.7395716
1 498 54 114 4 120 117.77 101.8935213
1 503 147 238 4 200 198.6 100.7049345
1 516 62 135 4 200 198.6 100.7049345
1 520 30 104 4 10 7.98 125.3132832
1 528 38 106 4 15 12.77 117.4628035
;
run;

proc sort data=have;
  by ID P_ID;
run;

data group_geomean;
  set have;
  by ID P_ID;
  
  if first.P_ID then do;
    product = 1;
    count = 0;
  end;

  product = product * rel;
  count + 1;

  if last.P_ID then do;
    Geo = product ** (1/count);
    output;
  end;
run;

i want calculate geomean base on ID P_ID

Geo
104.4496
110.1306
110.1306
110.1306
100.7049
100.7049
125.3133
117.4628

[FreelanceReinh · Accepted solution]

Hello @Daily1,

Add a RETAIN statement for variable product to your DATA step:

retain product;

Or use PROC UNIVARIATE to compute the geometric mean and  then merge the result back to the initial dataset:

proc univariate data=have noprint;
by id p_id;
var rel;
output out=gm geomean=Geo;
run;

data want;
merge have gm;
by id p_id;
if last.p_id; /* to obtain only one observation per BY group */ 
run;

[Daily1]

This output create on excel . i need same result on sas eg 

[FreelanceReinh]

Then use the PROC UNIVARIATE approach and omit the subsetting IF statement (if last.p_id;) in the DATA step.